Home » RDBMS Server » Security » Roles Question
Roles Question [message #5344] Tue, 11 February 2003 02:57 Go to next message
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 Go to previous messageGo to next message
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 Go to previous message
Barbara Boehmer
Messages: 9077
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> 
Previous Topic: grant execute
Next Topic: Can I encrypt user password with : password('testpassword') ?
Goto Forum:
  


Current Time: Thu Mar 28 11:26:13 CDT 2024