Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In today’s article, we will explain concepts such as defining roles in Oracle and which users have which roles.
When you want to grant the privileges in the role to all users, they are granted as PUBLIC.
1 2 3 | CREATE ROLE HR_EMP; GRANT SELECT ON HR.EMPLOYEES TO HR_EMP; GRANT HR_EMP TO PUBLIC; |
As a result of this process, all users can now select the hr.employees table.
Role Definition
1. We create a role.
1 | CREATE ROLE GUN_ISIGI; |
2. Rights are granted to the role.
1 2 3 4 5 | GRANT DELETE ON IPTVMWC.EPG TO GUN_ISIGI; GRANT DELETE ON IPTVMWC.PROGRAM TO GUN_ISIGI; GRANT DELETE ON IPTVMWC.DVBSI_EVENT TO GUN_ISIGI; GRANT DELETE ON WEBTELMWCORE.PROGRAM TO GUN_ISIGI; GRANT DELETE ON WEBTELMWCORE.EPG TO GUN_ISIGI; |
3. The role is assigned to the user.
1 | GRANT GUN_ISIGI TO AITERZI; |
NOTES: After the role is created, there is no need for COMMIT operation. Because COMMIT command is only required after DML operations. GRANT is a DCL operation.
4. After the role is created, it is checked whether it has been created in the following places.
a. From Sqlplus;
1 | select * from dba_roles order by 1; |
b. From TOAD; it is checked whether the Role came from Users and whether the Object Grants are correct from Roles.
Which Users Have Which Roles?
There are 2 ways to find out which users have which roles.
1. SQL Plus and the other is TOAD.
It can be seen in the interface in TOAD.
It can be seen in SqlPlus with the following command.
The columns of the table from which the information will be taken are seen.
1 2 3 4 5 6 7 | SQL> DESC DBA_ROLE_PRIVS; Name Null? Type ----------------------------------------- -------- ---------------------------- GRANTEE VARCHAR2(30) GRANTED_ROLE NOT NULL VARCHAR2(30) ADMIN_OPTION VARCHAR2(3) DEFAULT_ROLE |
2. The following command is run and all users are seen.
1 | SQL> SELECT * FROM DBA_ROLE_PRIVS; |
