Howto limit CREATE / ALTER ANY (PROCEDURE/VIEW...) to allow only specified schemas?

Stanislav Studený

Hello.

It there a way to limit CREATE / ALTER ANY (PROCEDURE/VIEW...) granted to USER1 to allow only CREATE/ALTER in specified schemas?

E.g. Something which will solve something like this : GRANT CREATE ANY PROCEDURE TO USER1 "ON USER2,USER3..".

Best Regards,

Stanislav

Emad Al-Mousa

Hi,

there is no system privilege that will allow you to do that (through SQL Syntax).... create any will give full authority to create any object under ALL DB users.

I would suggest the following to the let the user/account you want to create objects to "proxy" to certain schemas only.

for example, let us say the account is called USER_APP would like to create objects under SCHEMA1,SCHEMA2 only:

SQL> alter user SCHEMA1 grant connect through USER_APP;

SQL>  alter user SCHEMA2 grant connect through USER_APP;

after that your connection parameter would be USER_APP[SCHEMA1] so USER_APP will be able to create database objects under SCHEMA1 and the objects ofcourse will be owned by SCHEMA1....i hope this will help you

pmdba

Database Vault may allow you to limit the scope of "any" privileges, but it is an extra cost item. The use of proxy access as described by @Emad Al-Mousa is a better, less expensive way to go. It has the added benefit of allowing developers to have access to application schemas without having to share a common password: each developer logs in with their own credentials and never needs to know the app schema password.

Stanislav Studený

Hi.

Yes, I know about proxy user and this is concept we actually do use, but there are other problems one should be aware of (and we don't want to take that risk) :

1. Granting proxy allow proxy user to grant priviledges behalf of SCHEMA1 user.

2. Give you full power of SCHEMA1 user including select/update/insert/delete in tables of SCHEMA1.

And this is usually not posibble when user is limited with it's own grants (mostly through role).

Thanks for pointing me to the Database Vault.

Regards,

Stanislav

pmdba

Proxy users should only be used in development environments, where hopefully the ability to manipulate data is permissible, even desirable. Developers should not have access to change anything directly in a production environment, ever.