Roles Question [message #5344] |
Tue, 11 February 2003 02:57 |
Peter
Messages: 62 Registered: August 2000
|
Member |
|
|
Hi anybody knows the answer to this.
* create role manager;
create role clerk;
create role inventory;
create user scott identified by tiger;
grant clerk to inventory;
grant manager to clerk;
grant inventory to scott;
how many roles does scott have?
For those of you who will answer 1 to the above. then why in tables user_role_privs all 3 rows are assigned to user scott. Does this table shows all roles assigned to a particular user.
Thanks
|
|
|
Re: Roles Question [message #5355 is a reply to message #5344] |
Tue, 11 February 2003 10:22 |
billh
Messages: 35 Registered: August 1999
|
Member |
|
|
Scott has zero roles after this exercise.
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
With the Partitioning option
JServer Release 8.1.7.3.0 - Production
SQL> create role manager;
Role created.
SQL> create role clerk;
Role created.
SQL> create role inventory;
Role created.
SQL> create user scott identified by tiger;
User created.
SQL> grant clerk to inventory;
Grant succeeded.
SQL> grant manager to clerk;
Grant succeeded.
SQL> grant inventory to scott;
Grant succeeded.
SQL> select username, granted_role from user_role_privs where username = 'SCOTT' ;
no rows selected
|
|
|
Re: Roles Question [message #5372 is a reply to message #5344] |
Wed, 12 February 2003 01:18 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
Please see the following test below. I used TESTER instead of SCOTT. Notice that you have to grant create session to TESTER and connect as TESTER, in order to view TESTER's roles through the user_role_privs table. The result is that TESTER has only the one role INVENTORY. However, INVENTORY has all of the privileges of CLERK, which has all of the privileges of MANAGER, because MANAGER was granted to CLERK and CLERK was granted to INVENTORY. It's all a question of semantics. Although he only has the one role, he has the privilegs of all three roles.
SQL> CREATE ROLE MANAGER;
Role created.
SQL> CREATE ROLE CLERK;
Role created.
SQL> CREATE ROLE INVENTORY;
Role created.
SQL> CREATE USER TESTER IDENTIFIED BY TESTER;
User created.
SQL> GRANT CLERK TO INVENTORY;
Grant succeeded.
SQL> GRANT MANAGER TO CLERK;
Grant succeeded.
SQL> GRANT INVENTORY TO TESTER;
Grant succeeded.
SQL> GRANT CREATE SESSION TO TESTER;
Grant succeeded.
SQL> CONNECT TESTER/TESTER
Connected.
SQL> SELECT * FROM user_role_privs
2 WHERE username = 'TESTER';
USERNAME GRANTED_ROLE ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
TESTER INVENTORY NO YES NO
SQL> SELECT * FROM role_role_privs
2 WHERE role = 'INVENTORY';
ROLE GRANTED_ROLE ADM
------------------------------ ------------------------------ ---
INVENTORY CLERK NO
SQL> SELECT * FROM role_role_privs
2 WHERE role = 'CLERK';
ROLE GRANTED_ROLE ADM
------------------------------ ------------------------------ ---
CLERK MANAGER NO
SQL>
|
|
|