Forum Stats

  • 3,817,367 Users
  • 2,259,322 Discussions
  • 7,893,760 Comments

Discussions

Simulating Create or Replace for Users and Roles

Colin Bowern
Colin Bowern Member Posts: 7
edited Aug 26, 2009 4:47PM in SQL & PL/SQL
I am really getting frustraited by all of the quirks in Oracle (my background is SQL Server) that are throwing me curve balls.. :) I am trying to create a procedure to enable me to create a user or role as part of a setup script. I don't want the script to stop if the user or role already exists. My first attempt based on [a StackOverflow.com post|http://stackoverflow.com/questions/1197236/create-or-replace-role] was:

CREATE OR REPLACE PROCEDURE Create_Role( role_name IN VARCHAR2 ) IS PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
EXECUTE IMMEDIATE 'CREATE ROLE'||role_name;
EXCEPTION
WHEN OTHERS THEN
-- ORA-01921: role name '%1' conflicts with another user or role name.
IF SQLCODE <> -01921 THEN
RAISE;
END IF;
END Create_Role;
/
GRANT EXECUTE ON Create_Role TO Public;

I ran into permissions issues around the dynamic execute statement for Create Role that I can't figure out why (since the calling user is the system user):

SQL Error: ORA-01031: insufficient privileges
ORA-06512: at "SYSTEM.CREATE_ROLE", line 8
01031. 00000 - "insufficient privileges"

I tried a difference approach as exceptions don't feel like they are suitable flow control in my experience:

CREATE OR REPLACE PROCEDURE Create_Role( Role_Name IN VARCHAR2 ) IS PRAGMA AUTONOMOUS_TRANSACTION;
DECLARE
Row_Count NUMBER;
BEGIN
SELECT COUNT(*) INTO Row_Count FROM DBA_Roles WHERE UPPER(Rolename) = Role_Name;
IF (Row_Count == 0) THEN
EXECUTE IMMEDIATE 'CREATE ROLE '||role_name;
END IF;
END Create_Role;
/
GRANT EXECUTE ON Create_Role TO Public;

Now I am getting an "ORA-00942: table or view does not exist" because I do not have access to DBA_Roles directly (but through a role). I then read that procedures aren't able to calculate effective permissions from the roles when compiling ([www.adp-gmbh.ch/ora/err/ora_00942.html]). Honestly I'm not sure why it matters as the only thing that should be relevant is the permissions of the calling user at runtime.

Can someone help me figure out either the invalid permission

Edited by: user11095648 on Aug 26, 2009 11:54 AM

Edited by: user11095648 on Aug 26, 2009 11:54 AM
Tagged:

Answers

  • JustinCave
    JustinCave Member Posts: 30,293 Gold Crown
    A few comments...

    1) I don't see what the PRAGMA AUTONOMOUS_TRANSACTION buys you in the first case, but perhaps it is useful depending on how you are building your "setup script". Normally, "setup script" implies a SQL*Plus script, but I suspect that's not what you're intending.

    2) You do not want to give PUBLIC the ability to create a role.
    Honestly I'm not sure why it matters as the only thing that should be relevant is the permissions of the calling user at runtime.
    3) That's not the case. By default, Oracle stored procedures are definer's rights stored procedures. The procedure runs with the privileges of the definer (the owner of the procedure). And since roles can be enabled and disabled at a session level, definer's rights stored procedures don't use privileges granted via roles-- otherwise, you'd have to store off what roles were enabled for the session when the procedure was compiled, which of those had been revoked from the user, etc. So a definer's rights procedure only knows about the privileges granted directly to the definer.

    4) Both the ORA-01031 and ORA-00942 errors occur because the owner of the procedures (SYSTEM) has the CREATE ROLE privilege and the privilege to DBA_ROLES granted via a role, not directly. The owner of the procedure would need the appropriate privilege(s) granted directly, not via a role.

    5) You should never, ever create any objects owned by SYS or SYSTEM. In addition to not being set up as users that should be owning objects (which need direct grants), SYS and SYSTEM are special, so various rules don't work the same way for these users as for every other user in the system. I don't expect this is causing you any issues in the current snippet, but it will bite you in the future. In addition, upgrades can go haywire if you create objects in SYS or SYSTEM.

    So the end result is
    - You need to create a new user that will own the CREATE_ROLE procedure
    - You will need to grant this new user the CREATE ROLE privilege (for the first procedure) and/or the SELECT ANY DICTIONARY privilege (for the second procedure) directly.
    - You can then grant privileges on the CREATE ROLE procedure to whatever user or role you'd like.

    Justin
    JustinCave
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,935 Red Diamond
    edited Aug 26, 2009 12:37PM
    Hi,
    user11095648 wrote:

    IF (Row_Count == 0) THEN
    Did this work? The equality operator in Oracle is a single = .
    GRANT EXECUTE ON Create_Role TO Public;
    Are you really sure you want anyone who has a account to do this? (See discussion on AUTHID below.)
    Now I am getting an "ORA-00942: table or view does not exist" because I do not have access to DBA_Roles directly (but through a role). I then read that procedures aren't able to calculate effective permissions from the roles when compiling ([www.adp-gmbh.ch/ora/err/ora_00942.html]).
    That's correct. Roles don't count when you compile a stored procedure. Grant privileges on DBA_ROLES directly to the procedure owner.
    Honestly I'm not sure why it matters as the only thing that should be relevant is the permissions of the calling user at runtime.
    Procedures (and functions) created with AUTHID DEFINER (which is the default) are run wil the privileges of the procedure owner. The only privilege the caller needs is the privilege to EXECUTE the procedure. That is, if you could compile the procudeure, then the caller would not need any privileges on DBA_ROLES (either direct or through a role), and the caller would not need the CREATE ROLE system privilege. That's why you probably would not want to grant EXECUTE privileges on this procedure to PUBLIC.

    The alternative to AUTHID DEFINER is AUTHID CURRENT_USER, which you specify right before the keyword IS:
    CREATE OR REPLACE PROCEDURE Create_Role( Role_Name IN VARCHAR2 )
    AUTHID CURRENT_USER
    IS ...
    In procedures with AUTHID CURRENT_USER, it is the caller's privileges that are used at run time. The procedure owner still needs the privileges used in compiling the procedure, but not for running it. For example, if you added AUTHID CURRENT_USER as whown above, the procedure owner would still need SELECT privileges on dba_roles, since the query on dba_roles is coded into the procedure, but the procedure owner would not need CREATE ROLE. At run time, all that would matter is that the caller has the right privileges.
    Frank Kulash
  • Frank, Justin - Thanks for taking the time to reply. Your comments are very helpful.
    You need to create a new user that will own the CREATE_ROLE procedure
    Is there a best practice or naming convention for this sort of setup user? My initial thoughts is to create an AppAdmin user. The reason I wanted to create this at the system level is to keep it as a utiliity function.
    By default, Oracle stored procedures are definer's rights stored procedures.
    I guess I think about it in the sense of how processes execute under the operating system. The process may try to format the hard drive but if the user doesn't have access the result is that it can't complete.
    Are you really sure you want anyone who has a account to do this? (See discussion on AUTHID below.)
    Based on the assumption (admittedly without any evidence) that the user rights would be calculated into the mix I assigned it to the public hoping that it would fail if you don't have the actual rights. Rethinking this based on both of your comments.


    Next steps I think I will experiment with AUTHID CURRENT USER and setting up an administrative user account to own the various objects.

    Thanks for your help!
    Colin
  • JustinCave
    JustinCave Member Posts: 30,293 Gold Crown
    Is there a best practice or naming convention for this sort of setup user? My initial thoughts is to create an AppAdmin user. The reason I wanted to create this at the system level is to keep it as a utiliity function.
    I don't know that there is any sort of convention here. It's relatively uncommon to create stored procedures that you then call to install an application, so you're already rather non-conventional at this point. If you are going to have a bunch of setup procedures that are not going to be owned by the user that eventually owns all your application objects, AppAdmin seems reasonable (I assume that you would replace the App in AppAdmin with the name of your actual application, though, in case someone wanted to have multiple applications in the same database).

    Justin
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,935 Red Diamond
    Hi, Colin,
    user11095648 wrote:
    Is there a best practice or naming convention for this sort of setup user? My initial thoughts is to create an AppAdmin user. The reason I wanted to create this at the system level is to keep it as a utiliity function.
    Creating a user like AppAdmin is a great idea; lots of people do that. I've never heard of a convention for naming such a user.
    As Justin said, it should not be SYSTEM. To give just one example: if you create a new database, and want to copy all your custom procedures, it's easy to export the entire AppAdmin schema.
    You can create public synonyms for utility functions, so users don't have to know that they're owned by AppAdmin.
    Based on the assumption (admittedly without any evidence) that the user rights would be calculated into the mix I assigned it to the public hoping that it would fail if you don't have the actual rights. Rethinking this based on both of your comments.
    Writing procedures that are designed to fail if the user doesn't have the right privileges is okay; you just have to make them AUTHID CURRENT_USER.
    For something like creating users or roles, I would still be nervous about granting privileges to PUBLIC. Why can't you create a role, like ROLE_MAKER, and assign that role to the users who need ot run this procedure?
  • For something like creating users or roles, I would still be nervous about granting privileges to PUBLIC. Why can't you create a role, like ROLE_MAKER, and assign that role to the users who need ot run this procedure?
    I am starting to back off the idea of creating a procedure for this given all that has been said and further experimentation. My current mission is to get this code block working:

    DECLARE RowCount NUMBER := 0;
    BEGIN
    SELECT COUNT(*) INTO RowCount FROM DBA_Users WHERE UPPER(username) = 'APPADMINUSER';
    IF (RowCount = 0) THEN
    CREATE USER APPADMINUSER IDENTIFIED BY Testing1234 DEFAULT TABLESPACE TestData TEMPORARY TABLESPACE TEMP;
    END IF;
    END;

    SQL Developer doesn't seem to like the Create User call in the If .. End If block at the moment. Still digging into why. I'll insert the results inline for now since I only have two users and roles to create (runtime and admin user, runtime and reporting roles). If things get more complex in the future I'll refactor it out to a procedure and deal with the added complexity then.

    Cheers,
    Colin
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,935 Red Diamond
    Hi, Colin,

    CREATE USER isn't a PL/SQL command; it's a SQL command.
    To run a SQL command from inside PL/SQL, you can use EXECUTE IMMEDIATE:
    EXECUTE IMMEDIATE 'CREATE USER APPADMINUSER IDENTIFIED BY Testing1234 DEFAULT TABLESPACE TestData TEMPORARY TABLESPACE TEMP';
    Notice that the string does not end with a semicolon, that is, the last character inside the quotes is not ';'.
  • For future reference if anyone comes across this thread here is what I was able to accomplish:
    -----
    SET ServerOutput ON;

    PROMPT -- Creating Test Data Tablespace;
    DECLARE RowCount NUMBER := 0;
    BEGIN
    SELECT COUNT(*) INTO RowCount FROM DBA_Tablespaces WHERE UPPER(Tablespace_Name) = 'TESTDATA';
    IF (RowCount > 0) THEN
    EXECUTE IMMEDIATE 'DROP TABLESPACE TestData INCLUDING CONTENTS AND DATAFILES';
    END IF;
    END;
    /
    CREATE TABLESPACE TestData DATAFILE 'C:\Temp\TestData.dbf' SIZE 1M AUTOEXTEND ON;
    /
    PROMPT -- Creating Administrative User;
    DECLARE RowCount NUMBER := 0;
    BEGIN
    SELECT COUNT(*) INTO RowCount FROM DBA_Users WHERE UPPER(Username) = 'App_ADMINUSER';
    IF (RowCount = 0) THEN
    EXECUTE IMMEDIATE 'CREATE USER App_AdminUser IDENTIFIED BY RunningTests DEFAULT TABLESPACE TestData TEMPORARY TABLESPACE TEMP';
    END IF;
    END;
    /
    GRANT UNLIMITED TABLESPACE TO App_AdminUser;
    GRANT CREATE ROLE TO App_AdminUser;
    GRANT CREATE SESSION TO App_AdminUser;
    GRANT CREATE TABLE TO App_AdminUser;
    GRANT CREATE USER TO App_AdminUser;
    GRANT ALTER USER TO App_AdminUser;
    GRANT CREATE VIEW TO App_AdminUser;
    GRANT GRANT ANY PRIVILEGE TO App_AdminUser;
    GRANT GRANT ANY ROLE TO App_AdminUser;
    GRANT SELECT_CATALOG_ROLE TO App_AdminUser;
    /
    PROMPT -- Changing User Context to Administrative User;
    CONNECT App_AdminUser/RunningTests;
    /
    PROMPT -- Creating Runtime Role;
    DECLARE RowCount NUMBER := 0;
    BEGIN
    SELECT COUNT(*) INTO RowCount FROM DBA_Roles WHERE UPPER(Role) = 'App_RUNTIME';
    IF (RowCount = 0) THEN
    EXECUTE IMMEDIATE 'CREATE ROLE App_Runtime';
    END IF;
    END;
    /
    PROMPT -- Creating Reporting Role;
    DECLARE RowCount NUMBER := 0;
    BEGIN
    SELECT COUNT(*) INTO RowCount FROM DBA_Roles WHERE UPPER(Role) = 'App_REPORTING';
    IF (RowCount = 0) THEN
    EXECUTE IMMEDIATE 'CREATE ROLE App_Reporting';
    END IF;
    END;
    /
    PROMPT -- Creating Runtime User;
    DECLARE RowCount NUMBER := 0;
    BEGIN
    SELECT COUNT(*) INTO RowCount FROM DBA_Users WHERE UPPER(Username) = 'App_RUNTIMEUSER';
    IF (RowCount = 0) THEN
    EXECUTE IMMEDIATE 'CREATE USER App_RuntimeUser IDENTIFIED BY RunningTests DEFAULT TABLESPACE TestData TEMPORARY TABLESPACE TEMP';
    END IF;
    END;
    /
    GRANT UNLIMITED TABLESPACE TO App_RuntimeUser;
    GRANT App_Runtime TO App_RuntimeUser;
    -----

    This should give me a good start to automating some database-dependent tests in our project. Ideally we create a new tablespace, schema, insert sample data, run the dependent unit tests (i.e. integration with Oracle Text functionality), and then tear it on down.
This discussion has been closed.