Forum Stats

  • 3,853,560 Users
  • 2,264,238 Discussions
  • 7,905,390 Comments

Discussions

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

Stanislav Studený
Stanislav Studený Member Posts: 60 Bronze Badge
edited Jun 23, 2020 10:34PM in Database Security - General

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

pmdba

Answers

  • Emad Al-Mousa
    Emad Al-Mousa Member Posts: 716 Bronze Trophy
    edited Jun 20, 2020 2:10AM

    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
  • pmdba
    pmdba Member Posts: 103 Bronze Badge
    edited Jun 20, 2020 10:47PM

    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ý
    Stanislav Studený Member Posts: 60 Bronze Badge
    edited Jun 23, 2020 2:54AM

    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
    pmdba Member Posts: 103 Bronze Badge
    edited Jun 23, 2020 10:34PM

    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.