13 Replies Latest reply: Jan 11, 2013 2:10 PM by EdStevens RSS

    Granting role to user error

    aziz
      Oracle 10.2.05
      Linux environment

      I just granted a role to a user, but the user does not have privileges base on the role.

      Here is what I did:

      First create a user (db_user) using system id
      Second, create role schema_admin_role
      Then run the script to grant privileges to the role
      (SELECT 'grant select, insert, update, delete on ' ||owner|| '.'||table_name || ' to schema_admin_role;' from dba_tables WHERE OWNER = 'another_schema';

      Then run
      grant schema_admin_role to db_user;

      The problem:
      When db_user tries to update table X own by another_schema, he gets not sufficent privileges

      But when I run (select owner, table_name,privilege from dba_tab_privs where grantee = 'SCHEMA_ADMIN_ROLE'; ), I see all the privileges owned by this role.

      Any solution from your end will be appreciated.
        • 1. Re: Granting role to user error
          sb92075
          Albert_Zaza wrote:

          The problem:
          When db_user tries to update table X own by another_schema, he gets not sufficent privileges
          do NOT tell use what you think was done.
          SHOW us using COPY & PASTE exactly was done & how Oracle responded.
          • 2. Re: Granting role to user error
            aziz
            >
            do NOT tell use what you think was done.
            SHOW us using COPY & PASTE exactly was done & how Oracle responded.
            What do you want to know - I have pains takenly wrote down what Oracle responds was you dont get it.

            Again - a user is granted accesss to a particular role, but that user is unable to use the privileges from such role - what's in that you don't understand?
            • 3. Re: Granting role to user error
              sb92075
              did db_user start a new session after GRANT was issued?
              • 4. Re: Granting role to user error
                aziz
                sb92075 wrote:
                did db_user start a new session after GRANT was issued?
                Yes he did - also when I try to list all privileges granted to db_user, I get no row seleted. On the other hand, when I query privileges granted to role schema_admin_role, I see all privileges granted earlier
                example

                select owner, table_name,privilege from dba_tab_privs where grantee = 'SCHEMA_ADMIN_ROLE'; ---Here we get all privileges

                select owner, table_name,privilege from dba_tab_privs where grantee = 'DB_USER'; --No row seleted                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
                • 5. Re: Granting role to user error
                  982500
                  DBA_TAB_PRIVS do not list privileges about roles that user have, just the privileges granted directly to him.
                  You have to query DBA_ROLE_PRIVS to see if the user have that role granted.

                  select * from DBA_ROLE_PRIVS where GRANTEE='DB_USER';

                  and check that role to see its privileges.
                  • 6. Re: Granting role to user error
                    EmaxG
                    As you were told already, this works and you must be doing something wrong. Please copy and paste what you did, not what you think you did.
                    SQL> CREATE USER A IDENTIFIED BY A;
                    
                    User created.
                    
                    SQL> GRANT UNLIMITED TABLESPACE TO A;
                    
                    Grant succeeded.
                    
                    SQL> CREATE TABLE A.TEST (TEST VARCHAR2(2));
                    
                    Table created.
                    
                    SQL> INSERT INTO A.TEST VALUES ('Hi');
                    
                    1 row created.
                    
                    SQL> commit;
                    
                    Commit complete.
                    
                    SQL> CREATE USER B IDENTIFIED BY B;
                    
                    User created.
                    
                    SQL> CREATE ROLE A_ROLE ;
                    
                    Role created.
                    
                    SQL> GRANT SELECT, UPDATE, INSERT, DELETE ON A.TEST TO A_ROLE;
                    
                    Grant succeeded.
                    
                    SQL> GRANT A_ROLE TO B;
                    
                    Grant succeeded.
                    
                    SQL> GRANT RESOURCE, CONNECT TO B;
                    
                    Grant succeeded.
                    
                    SQL> CONN B/B
                    Connected.
                    SQL> SELECT * FROM A.TEST;
                    
                    TE
                    --
                    Hi
                    
                    SQL> INSERT INTO A.TEST VALUES ('Ho');
                    
                    1 row created.
                    
                    SQL> COMMIT;
                    
                    Commit complete.
                    
                    SQL> SELECT * FROM A.TEST;
                    
                    TE
                    --
                    Hi
                    Ho
                    
                    SQL> DELETE FROM A.TEST;
                    
                    2 rows deleted.
                    
                    SQL> COMMIT;
                    
                    Commit complete.
                    
                    SQL> SELECT * FROM A.TEST;
                    
                    no rows selected
                    • 7. Re: Granting role to user error
                      aziz
                      Krulikowski wrote:
                      DBA_TAB_PRIVS do not list privileges about roles that user have, just the privileges granted directly to him.
                      You have to query DBA_ROLE_PRIVS to see if the user have that role granted.

                      select * from DBA_ROLE_PRIVS where GRANTEE='DB_USER';

                      and check that role to see its privileges.
                      I did and the role schema_admin_role is listed, but with "Admin Option" & "Default_Role" saying NO
                      • 8. Re: Granting role to user error
                        EmaxG
                        Albert_Zaza wrote:
                        Krulikowski wrote:
                        DBA_TAB_PRIVS do not list privileges about roles that user have, just the privileges granted directly to him.
                        You have to query DBA_ROLE_PRIVS to see if the user have that role granted.

                        select * from DBA_ROLE_PRIVS where GRANTEE='DB_USER';

                        and check that role to see its privileges.
                        I did and the role schema_admin_role is listed, but with "Admin Option" & "Default_Role" saying NO
                        Login as DB_USER and do SET ROLE <ROLENAME> and see if that fixes it then.
                        • 9. Re: Granting role to user error
                          aziz
                          N K wrote:
                          Albert_Zaza wrote:
                          Krulikowski wrote:
                          DBA_TAB_PRIVS do not list privileges about roles that user have, just the privileges granted directly to him.
                          You have to query DBA_ROLE_PRIVS to see if the user have that role granted.

                          select * from DBA_ROLE_PRIVS where GRANTEE='DB_USER';

                          and check that role to see its privileges.
                          I did and the role schema_admin_role is listed, but with "Admin Option" & "Default_Role" saying NO
                          Login as DB_USER and do SET ROLE <ROLENAME> and see if that fixes it then.
                          Thanks N K,
                          I think this might be working - we are in the trying phase. I will update soon.
                          • 10. Re: Granting role to user error
                            aziz
                            >
                            Thanks N K,
                            I think this might be working - we are in the trying phase. I will update soon.
                            Ok here is what I got: It works when I used SQLplus client, but the following error when I used SQL Developer:

                            DELETE shema_user.district_mapping
                            WHERE rownum <= 1;

                            Error starting at line 1 in command:
                            DELETE shema_user.district_mapping
                            WHERE rownum <= 1


                            Error at Command Line:1 Column:16
                            Error report:
                            SQL Error: ORA-01031: insufficient privileges
                            01031. 00000 - "insufficient privileges"
                            *Cause:    An attempt was made to change the current username or password
                            without the appropriate privilege. This error also occurs if
                            attempting to install a database without the necessary operating
                            system privileges.
                            When Trusted Oracle is configure in DBMS MAC, this error may occur
                            if the user was granted the necessary privilege at a higher label
                            than the current login.
                            *Action:   Ask the database administrator to perform the operation or grant
                            the required privileges.
                            For Trusted Oracle users getting this error although granted the
                            the appropriate privilege at a higher label, ask the database
                            administrator to regrant the privilege at the appropriate label.
                            • 11. Re: Granting role to user error
                              EmaxG
                              ALTER USER <USER> DEFAULT ROLE <ROLE>
                              This will do the trick so the role is enabled by default for that user. Then you will not have to set it.

                              Edited by: N K on Jan 11, 2013 9:21 AM
                              • 12. Re: Granting role to user error
                                aziz
                                N K wrote:
                                ALTER USER <USER> DEFAULT ROLE <ROLE>
                                This will do the trick so the role is enabled by default for that user. Then you will not have to set it.
                                It did't help, but what solved the problem is to directly grant the privileges to the user.

                                >
                                SELECT 'grant select, insert, update, delete on ' ||owner|| '.'||table_name || ' to db_user;' from dba_tables WHERE OWNER = 'schema_ower';
                                • 13. Re: Granting role to user error
                                  EdStevens
                                  Albert_Zaza wrote:
                                  N K wrote:
                                  ALTER USER <USER> DEFAULT ROLE <ROLE>
                                  This will do the trick so the role is enabled by default for that user. Then you will not have to set it.
                                  It did't help, but what solved the problem is to directly grant the privileges to the user.
                                  SELECT 'grant select, insert, update, delete on ' ||owner|| '.'||table_name || ' to db_user;' from dba_tables WHERE OWNER = 'schema_ower';
                                  Sounds like there was some stored procedure/trigger involved.