Database Roles Question
Date: 1996/07/11
Message-ID: <4s3ocr$aqt_at_apollo.isisnet.com>#1/1
Can somebody explain this one to me?
I have 2 database roles (ROLE1 and ROLE2) with no system privileges and no object privileges. ROLE1 has the CONNECT role granted to it. ROLE1 and 2 have been granted to USER1 and so the user can connect. Now I want to change USER1 so that only ROLE1 is the default role. What I see as the same thing gives me two results...
- ALL EXCEPT method
SQL> alter user user1 default role all except role2;
User altered.
SQL> select * from dba_role_privs
2 where grantee in
3 (select granted_role from dba_role_privs 4 where grantee = 'USER1')
5 or grantee = 'USER1';
GRANTEE GRANTED ADM DEF
------- ------- --- ---
ROLE1 CONNECT NO YES
USER1 ROLE1 NO YES
USER1 ROLE2 NO NO
SQL> connect user1/user1
ERROR: ORA-01045: user USER1 lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.
2) Not the ALL EXCEPT method
SQL> alter user user1 default role role1;
User altered.
SQL> select * from dba_role_privs
2 where grantee in
3 (select granted_role from dba_role_privs 4 where grantee = 'USER1')
5 or grantee = 'USER1';
GRANTEE GRANTED ADM DEF
------- ------- --- ---
ROLE1 CONNECT NO YES
USER1 ROLE1 NO YES
USER1 ROLE2 NO NO
SQL> connect user1/user1
Connected.
I apologize for the length of this post but can somebody tell me what is different??
--
Murray Kaiser | Usual gutless disclaimer.. Nova Scotia Power | Opinions are mine and not necessarily(Murray.Kaiser_at_NSPower.NS.CA) | shared by my employer Received on Thu Jul 11 1996 - 00:00:00 CEST