This discussion is archived
13 Replies Latest reply: Jan 11, 2013 12:10 PM by EdStevens RSS

Granting role to user error

aziz Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    >
    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 Guru
    Currently Being Moderated
    did db_user start a new session after GRANT was issued?
  • 4. Re: Granting role to user error
    aziz Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    N K Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    N K Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    >
    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
    N K Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points