Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

User can not inherited privilege from Role

1047739Oct 15 2013 — edited Oct 17 2013

DD1 is a new user, CT_GROUP_USER is a role with all tables access right.


1)First, check the privilege of role CT_GROUP_USER
select table_name,privilege from dba_tab_privs where grantee='CT_GROUP_USER'
we can see CT_GROUP_USER have ALL tables' privilege.

2)Second, grant CT_GROUP_USER role to user DD1
GRANT ct_group_ADMINISTRATOR to DD1 with admin option
GRANT ct_group_USER to DD1 with admin option
select * from dba_role_privs where grantee='DD1'
we can see CT_GROUP_USER role here


3)
USE DD1 to access table ct_user, it looks DD1 did not have privilege inherited from  CT_GROUP_USER


4) Do additional operation, grant a table privilege to DD1
grant select,insert,update,delete on CT_ACLENTRY     to DD1 WITH GRANT OPTION
select table_name,privilege from dba_tab_privs where grantee='DD1'
DD1 ONLY have CT_ACLENTRY  privilege.
USE DD1 to access ct_aclentry, it is succeed.

5) RUN below script on Oracle 10g and Oracle 11g, User DD3 can access tables on 10g but failed on 11g.
CREATE USER DD3 IDENTIFIED BY DD3
GRANT CREATE SESSION TO DD3
GRANT CT_GROUP_ADMINISTRATOR TO DD3
GRANT CT_GROUP_USER TO DD3

Question: Is there any setting for GRANT on Oracle 11g?


Additional: ALTER USER DD3 DEFAULT ROLE CT_GROUP_USER
Above command can not let DD3 access tables, DD1 neither

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 14 2013
Added on Oct 15 2013
20 comments
2,288 views