Home » RDBMS Server » Server Administration » Data Dictioanry View Doubt
Data Dictioanry View Doubt [message #217705] Sun, 04 February 2007 23:27 Go to next message
shahnazurs
Messages: 240
Registered: June 2005
Location: India
Senior Member
Hi,
I can see the field default_role in the data dictionary view dba_role_privs.
Could you please let me know what is the use of this field when it is YES/No.

Thanks.
Re: Data Dictioanry View Doubt [message #217706 is a reply to message #217705] Sun, 04 February 2007 23:44 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
dba_role_privs.DEFAULT_ROLE show which ROLES is DEFAULT for users.
by default all grant all is default for user but when you explicit specify DEFAULT ROLE for user then only specify role is DEFAULT role for user.
consider.
SQL> select grantee, granted_role , default_role from dba_role_privs
  2  where grantee = 'SCOTT';

GRANTEE                        GRANTED_ROLE                   DEF
------------------------------ ------------------------------ ---
SCOTT                          DBA                            YES
SCOTT                          DEF                            YES
SCOTT                          CONNECT                        YES
SCOTT                          RESOURCE                       YES

SQL> alter user SCOTT DEFAULT ROLE resource;

User altered.

SQL> select grantee, granted_role , default_role from dba_role_privs
  2  where grantee = 'SCOTT';

GRANTEE                        GRANTED_ROLE                   DEF
------------------------------ ------------------------------ ---
SCOTT                          DBA                            NO
SCOTT                          DEF                            NO
SCOTT                          CONNECT                        NO
SCOTT                          RESOURCE                       YES

SQL> alter user SCOTT DEFAULT ROLE def;

User altered.

SQL> select grantee, granted_role , default_role from dba_role_privs
  2  where grantee = 'SCOTT';

GRANTEE                        GRANTED_ROLE                   DEF
------------------------------ ------------------------------ ---
SCOTT                          DBA                            NO
SCOTT                          DEF                            YES
SCOTT                          CONNECT                        NO
SCOTT                          RESOURCE                       NO

SQL>


regards
Taj
Re: Data Dictioanry View Doubt [message #217730 is a reply to message #217706] Mon, 05 February 2007 02:42 Go to previous messageGo to next message
shahnazurs
Messages: 240
Registered: June 2005
Location: India
Senior Member
Thanks TAJ for this. Let us say
select privilege of emp table has granted to a role R1

then we are granting the same role to user A

can the user use the role A if the value of default_role is NO?

Thanks
Re: Data Dictioanry View Doubt [message #217733 is a reply to message #217730] Mon, 05 February 2007 03:10 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
can the user use the role A if the value of default_role is NO?
Yes

regards
Taj

Re: Data Dictioanry View Doubt [message #217738 is a reply to message #217733] Mon, 05 February 2007 03:34 Go to previous messageGo to next message
shahnazurs
Messages: 240
Registered: June 2005
Location: India
Senior Member
Hi,
I have simulated the above scenario

I confirmed user A is having roles
connect,resourse,r1

where r1 is having select privilege on emp

GRANTEE GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE
A R1 NO YES
A CONNECT NO NO
A RESOURCE NO NO

Now the user A unable to login?

User A lacks CREATE SESSION PRIVILAGE;login denied

Could you tell me this?

Thanks
Re: Data Dictioanry View Doubt [message #217754 is a reply to message #217738] Mon, 05 February 2007 05:10 Go to previous messageGo to next message
Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If I'm not wrong about it, both CONNECT and RESOURCE roles are here for backward compatibility, but they have lost all privileges which existed in previous Oracle versions.

To be able to use those roles as previously, you'd have to grant privileges to roles and then grant roles to users. Or, grant privileges one by one directly to user.
Re: Data Dictioanry View Doubt [message #217764 is a reply to message #217738] Mon, 05 February 2007 06:09 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
yes it is expected behaviour.
That is why every privs is DEFAULT_ROLE column YES for users.
or if you overwrite above situation then you have to define MINIMUM privs ROLE DEFAULT role for user.

regards
Taj
Re: Data Dictioanry View Doubt [message #217924 is a reply to message #217705] Tue, 06 February 2007 02:19 Go to previous message
shahnazurs
Messages: 240
Registered: June 2005
Location: India
Senior Member
Thank you all for your prompt response.
Previous Topic: How to Export query result to csv file
Next Topic: SPFILE or PFILE
Goto Forum:
  


Current Time: Fri Sep 20 04:51:58 CDT 2024