Home » RDBMS Server » Security » Role related question.
Role related question. [message #14781] Wed, 08 September 2004 10:15 Go to next message
Messages: 75
Registered: June 2001
1. How can i see what the privileges are in a particular role ?
Is there any data dict view to see the contents of a role ?

2. I want a role 'MANAGER' to have the ability to select from,insert into and update existing rows of the employees table in the hr schema. And i also want the person given this role pass it on to others.

How can i do these ?
Re: Role related question. [message #14785 is a reply to message #14781] Wed, 08 September 2004 14:05 Go to previous message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
scott@ORA92> -- create hr schema:
scott@ORA92> create user hr identified by hr
  2  /

User created.

scott@ORA92> -- grant privileges to hr:
scott@ORA92> grant connect, resource to hr
  2  /

Grant succeeded.

scott@ORA92> -- create employees table in hr schema:
scott@ORA92> create table hr.employees as select * from scott.emp where 1 = 2
  2  /

Table created.

scott@ORA92> -- create manager role:
scott@ORA92> <b>create role manager</b>
  2  /

Role created.

scott@ORA92> -- grant privileges to manager role:
scott@ORA92> <b>grant select, insert, update on hr.employees to manager</b>
  2  /

Grant succeeded.

scott@ORA92> -- see table privileges granted to manager role:
scott@ORA92> <b>select * from role_tab_privs where role = 'MANAGER'</b>
  2  /

ROLE                           OWNER                          TABLE_NAME
------------------------------ ------------------------------ ------------------------------
COLUMN_NAME                    PRIVILEGE                                GRA
------------------------------ ---------------------------------------- ---
MANAGER                        HR                             EMPLOYEES
                               INSERT                                   NO

MANAGER                        HR                             EMPLOYEES
                               SELECT                                   NO

MANAGER                        HR                             EMPLOYEES
                               UPDATE                                   NO

scott@ORA92> -- create user1 with connect privileges:
scott@ORA92> create user user1 identified by user1
  2  /

User created.

scott@ORA92> grant connect to user1
  2  /

Grant succeeded.

scott@ORA92> -- grant manager role to user1,
scott@ORA92> -- with permission to grant it to others:
scott@ORA92> <b>grant manager to user1 with admin option</b>
  2  /

Grant succeeded.

scott@ORA92> -- connect as user1:
scott@ORA92> connect user1/user1
scott@ORA92> @ login
user1@ORA92> -- test privileges:
user1@ORA92> insert into hr.employees (empno, ename) values (1, 'test1')
  2  /

1 row created.

user1@ORA92> update hr.employees set ename = 'test2' where empno = 1
  2  /

1 row updated.

user1@ORA92> select empno, ename from hr.employees
  2  /

---------- ----------
         1 test2

user1@ORA92> grant manager to scott
  2  /

Grant succeeded.
Previous Topic: AUDIT
Next Topic: Insufficient privilege while using truncate
Goto Forum:

Current Time: Mon Aug 15 07:30:26 CDT 2022