SkillBuilders | Oracle 9i Administration | Lesson 11: Managing Security

Instructor's Notes

(If applicable)

Lesson 11: Managing Security
This lesson is a continuation of your studies related to securing an Oracle database. You have already progressed through these lessons:

Creating Administration Users where you learned how to create users with SYSDBA privileges.
Creating and Managing Users where you learned how to create general users and grant basic privileges.
Password Management where you learned about password features such as password aging and password locking.
Introduction to Auditing where you learned how to keep track of operations conducted in/on your database.

This lesson complements those lessons with the following topics:

Basics – Here you will learn more about GRANT and REVOKE statements and the privilege types (system and object).
System Privileges – This section will teach you what system privileges are available and how to grant and revoke them.
Object Privileges – This section will teach you what object privileges are available and how to grant and revoke them.
Roles – Finally, you will learn about roles, a technique for grouping privileges under a single name.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

While this lesson provides a solid foundation in database security, there is quite a bit more to learn about securing an Oracle database:

Protecting the network against sniffing and spoofing. This would involve using firewalls, securing the listener, node filtering and more.
Virtual Private Database – This is a database-centric security logic feature. The basic functionality of FGAC is to, based on the circumstances that your logic determines, add a WHERE clause to the query (SELECT, UPDATE, INSERT and DELETE are all supported) being executed, thereby restricting the user to a set of rows.
Fine Grained Auditing – Mentioned briefly earlier in the lesson on auditing, this Oracle9i feature provides a technique for conditional auditing of SELECT-only operations. This allows us to determine what the user selected.
Encryption – Storing encrypted data in the database.

If you are responsible for security, consider purchasing the following text book to continue your studies on these subjects and more:
Oracle Privacy Security Auditing, Arup Nanda, ISBN 0-9727513-9-4.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

Access to the database is controlled with the concept of user accounts, typically referred to as "users".
After the DBA creates a user, the user needs to be granted privileges to connect and operate within the database. In this example, I create a user called ‘DAVE’ and give the user the ability to connect to the database via the GRANT CREATE SESSION statement. The CREATE USER parameters used are described here:

IDENTIFIED BY – Specify the initial password for the new user.
DEFAULT TABLESPACE – The tablespace where objects such as tables and clusters will, by default, be created. The user should also be provided QUOTA on this tablespace. The default DEFAULT TABLESPACE is the SYSTEM tablespace, which is not a good idea.
QUOTA – Allows the user to use disk space within the tablespace. QUOTA UNLIMITED allows the user to use as much space as is available in the tablespace. Alternatively, code QUOTA nM to limit the amount of space the user can use in the tablespace, where “n” is some number of megabytes.

Notes for this slide continue on the next page…

Other Text:
(Examples or comments displayed on slide, if any).

1 create user dave identified by dave
2 default tablespace users
3 temporary tablespace temp2
4 quota unlimited on users
5* password expire
SQL> /
User created.

SQL> grant create session to dave;
Grant succeeded.
Allow new user to logon to Oracle

Instructor's Notes

(If applicable)

Notes continued from the previous page:

TEMPORARY TABLESPACE – The name of the tablespace to be used for temporary disk segment requirements, such as sorting and temporary tables. The default temporary tablespace is controlled by the DEFAULT TEMPORARY TABLESPACE clause on the CREATE DATABASE command.
PASSWORD EXPIRE – Causes Oracle to prompt for a new password upon 1st logon attempt.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

Privileges are granted with the GRANT statement. In the first example shown above, I grant the CREATE TABLE system privilege to user ‘DAVE’. Privileges can be granted to (or revoked from) users and roles. (Roles are administrative groupings of privileges. Roles are covered in more detail later in this lesson.)
Privileges are taken away with the REVOKE statement. Use the REVOKE statement to revoke system and object privileges from users and roles. It can also be used to revoke roles from roles. Later in this lesson we will learn what the requirements for revoking privileges are.
Both the GRANT and REVOKE statements issue implicit commits – explicitly committing these operations is unnecessary. (In fact, issuing GRANT or REVOKE will commit any uncommitted statements, even if the GRANT or REVOKE fails.)
Refer to the Oracle9i SQL Reference manual for a complete description of the GRANT and REVOKE statements.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> revoke create table from dave;

Revoke succeeded.
GRANT/REVOKE implicitly commit

SQL> grant create table to dave;

Grant succeeded.

Instructor's Notes

(If applicable)

Oracle provides three types of privileges:

System privileges – These privileges allow the user to execute some action on the database. For example, logon requires CREATE SESSION privilege; creating a table requires CREATE TABLE privilege. System privileges are not tied to one specific object.
Object privileges – These privileges are related to one specific database object. In the second example shown above, I give user “DAVE" the ability to SELECT from the table PROD.CUSTOMER.
Special DBA privileges – In order for the DBA to startup or shutdown the database, the user must be granted and explicitly logon with either SYSDBA or SYSOPER. SYSDBA privilege was covered in detail earlier in this course in the lesson Creating Administrative Users.

Other Text:
(Examples or comments displayed on slide, if any).

grant create table to dave;

grant select on prod.customer TO dave;

grant sysdba to dave;

Instructor's Notes

(If applicable)

System privileges can be granted to a user or a role.
In order to grant a system privilege you must be an administrator of the privilege (see the ADMIN OPTION later in this lesson) or you must have the GRANT ANY PRIVILEGE system privilege.
A couple of terms you will need to be familiar with include:

GRANTOR – The user granting the privilege.
GRANTEE – The user(s) receiving the privilege.

This example demonstrates that multiple privileges can be specified in a single GRANT statement and multiple grantees can also be specified. After the above command has executed the Oracle users ‘SCOTT’ and ‘DAVE’ can use the CREATE TABLE statement (assuming quota on a tablespace has been provided) and the CREATE PROCEDURE statement.
Optional Mini-Workshop
- There are more than 100 system type privileges. Take a moment now to look at the system privileges listed with the GRANT statement in the Oracle9i SQL Reference manual. (Remember, you must have ready access to the Oracle manuals to be a successful DBA. If you have not yet acquired an account for the Oracle Technology Network at http://technet.oracle.com, do so now.)

Other Text:
(Examples or comments displayed on slide, if any).

SQL> connect system/dave
Connected.

SQL> grant create table, create procedure
2 to dave, scott
SQL> /

Grant succeeded.
dave and scott are the grantees
Grantor must be “administrator” of the privilege to grant it

Do the mini-workshop

Instructor's Notes

(If applicable)

As illustrated above, most system privileges can be granted with the keyword ‘ANY’. When an ANY-type privilege is granted, the grantee can use the privilege in any schema. The example above demonstrates this; user ‘DAVE’ creates a table in the ‘SCOTT’ schema.
ANY-type privileges are a separate privilege from the related non-ANY privilege. i.e., CREATE ANY TABLE and CREATE TABLE are two separate privileges. While it would be redundant, a user can possess both privileges.
Note that when creating an object in another schema, Oracle uses the quota and default tablespace of the schema owner. So, in the example above, user ‘SCOTT’ must have quota on his default tablespace for successful completion of the CREATE TABLE statement.
Finally, also note that a user who creates an object in another schema does not inherit object privileges on the object – even though they created the object. This can lead to the strange situation where a user creates a table yet cannot insert, update, delete or select from it. (If the creator has SELECT ANY TABLE privilege, INSERT ANY TABLE, DELETE ANY TABLE, then the user would be able to execute DML on the new object. But, that is not the same as having direct object privileges on the new object.)

Other Text:
(Examples or comments displayed on slide, if any).

SQL> grant create any table to dave;
Grant succeeded.

SQL> connect dave/dave
Connected.

SQL> create table scott.t (c1 number);
Table created.
DAVE creates SCOTT.T
SCOTT owns table T
SCOTT must have quota
User DAVE does not implicitly have object privileges on SCOTT.T

Instructor's Notes

(If applicable)

Privileges granted with the PUBLIC keyword are given to all users – existing and future users. Of course, the user must be able to connect to the database, so the user must also possess CREATE SESSION system privilege.
Once the privilege has been given to PUBLIC, individual users can not then be stopped from using that privilege.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> grant create table
2 to public;
Grant succeeded.
SQL>

Instructor's Notes

(If applicable)

System level privileges can also to be granted to a user with the optional “WITH ADMIN OPTION” clause. With this clause, the grantee (recipient) of the privilege becomes an administrator of the privilege. This means that the grantee can grant the privilege to any other user (with or without the ADMIN OPTION). The grantee can also revoke the privilege from other users - even the user who granted them the privilege.
Supplemental Notes
- Revoking system privileges does not cascade to users who have received the privilege from the user who is revoked.
- Another technique for giving a user the ability to grant and revoke system privileges is to grant the GRANT ANY PRIVILEGE system privilege. This is very powerful because the recipient can grant or revoke any system privilege from any user.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> connect dave/dave@class2
Connected.
SQL> grant create any table to scott with admin option;

Grant succeeded.

SQL> connect scott/tiger@class2
Connected.
SQL> revoke create any table from dave;

Revoke succeeded.

Instructor's Notes

(If applicable)

Use the REVOKE statement to take away privileges. You must be an administrator of a system privilege to successfully revoke it from another user. More than one system privilege and user name can be specified at one time in the command.
Any objects already created are not affected. Say, for example, that user DAVE creates a table T. Then user SYSTEM revokes the CREATE TABLE privilege from DAVE. Table DAVE.T remains intact.

Other Text:
(Examples or comments displayed on slide, if any).

1 select privilege, admin_option
2 from user_sys_privs
3* where admin_option = 'YES'
SQL> /

PRIVILEGE ADM
---------------------------------------- ---
CREATE TABLE YES
Contains sys privs for the current user

Instructor's Notes

(If applicable)

Several data dictionary views are helpful when dealing with system privileges. For example, SESSION_PRIVS contains all the active system privileges for your session. (System privileges can be granted via a role, and roles can be inactive; more on this later in this lesson.)

Other Text:
(Examples or comments displayed on slide, if any).

SQL> select * from session_privs;

PRIVILEGE
-----------------------------------
CREATE SESSION
ALTER SESSION
CREATE TABLE
SELECT ANY TABLE
. . .
QUERY REWRITE
SELECT ANY DICTIONARY

14 rows selected.

Instructor's Notes

(If applicable)

Additional helpful dictionary views include:

DBA_SYS_PRIVS - Records all system privileges granted to all users and all roles.
USER_SYS_PRIVS - System privileges granted to current user
ROLE_SYS_PRIVS - System privileges granted to roles

Supplemental Notes
- Use the following query to help find data dictionary tables related to privileges:
- 1 select table_name, comments
- 2 from dict
- 3* where table_name like '%PRIV%'

Other Text:
(Examples or comments displayed on slide, if any).

1 select privilege, admin_option
2 from dba_sys_privs
3* where grantee = 'DAVE'
SQL> /

PRIVILEGE ADM
---------------------------------------- ---
CREATE TABLE YES
CREATE SESSION NO
CREATE ANY TABLE NO
CREATE PROCEDURE NO

Instructor's Notes

(If applicable)

System Privileges Workshop
1. Logon to SQL*Plus with your student user.
2. List the system privileges currently in effect for your session.
3. Create a new user called TABLEMAN
4. Give the user the ability to logon and create tables in any schema
5. Logon to the new user.
6. Create a simple test table called “T” in the TABLEMAN schema.
7. While connected to the TABLEMAN schema, try to create a simple table in the SYSTEM schema. What happens? How would you permit this?

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

Object-level privileges give access to one specific Oracle object. Commonly used objects that are subject to object privileges are:

Table
View
Sequence
Procedures, Functions and Packages
Materialized View
Directory (directories are used for access to external tables)
Library (libraries are used when making calls to external programs from a PL/SQL procedures or functions.)

If you own an object (i.e. it is in your schema), you can grant object privileges on that object to other users. Additionally, Oracle9i Release 2 provides a new privilege called GRANT ANY OBJECT. This privilege allows a user to grant object privileges on any database object. This is a DBA-level privilege and should be used with great care.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> grant grant any object privilege to dave;

Grant succeeded.

Instructor's Notes

(If applicable)

This slide contains an abbreviated list of object types and related privileges. Refer to the GRANT statement in the Oracle9i SQL Reference manual for a complete list. Summarized descriptions:

The SELECT object-level privilege allows another Oracle user to query the data in the table. If this privilege is given, the Oracle user can query any column on any row in the table. Views can be used to restrict which columns and which rows can be queried. Another technique for limiting the rows a user can see is using the Virtual Private Database feature. Refer to the Oracle9i Security Overview for more information on Virtual Private Database.
The DELETE object-level privilege allows the deletion of any existing rows from the table. Use views to restrict which rows can be deleted.
The ALTER object-level privilege allows an Oracle user to alter the definition of a table or a sequence. The owner of the object will not usually allow other Oracle users to change the definition of the object - this option is not often used.
The INDEX object-level privilege allows another Oracle user to create indexes on the grantor’s tables.

Notes for this slide continues on next page…

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

Notes continued from the previous page:

The EXECUTE privilege gives a user the privilege to execute your stored procedure (or function or package).
The REFERENCES object-level privilege allows another Oracle user create a foreign key constraint that refers to the object. This may be used where the tables in one Oracle user require some foreign key constraints to be set up on another user’s table

Supplemental Notes
- Stored procedures and functions can be created either with Definer Rights or Invoker Rights:
  - With a Definer Rights procedures (the default), the grantee does not need privileges on any objects referenced within the procedural object. Definer rights procedures always use the schema and privilege set of the compiler (definer).
  - With Invoker Rights procedures, the grantee does need privileges on any objects referenced within the procedural object. The Invoker Rights procedure uses the schema and privilege set of the invoker, regardless of who compiled the procedure.
- Refer to the following for more information on definer rights and invoker rights:
  - Oracle9i PL/SQL User’s Guide and Reference for more into [AUTHID {CURRENT_USER | DEFINER}]
  - expert one-on-one Oracle by Tom Kyte

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

Use the GRANT statement to give one or more object-level privileges to one or more Oracle users or roles. You can grant all relevant object privileges (e.g. for tables that is SELECT UPDATE, DELETE, INSERT, etc) with the ALL keyword (shown above). You can grant a privilege to all users by using “PUBLIC” as the grantee.
There are restrictions. For example, you cannot grant privileges on an object that you do not own unless:

You were granted the privilege with the WITH GRANT OPTION (discussed later in this lesson).
You were granted the GRANT ANY OBJECT PRIVILEGE

Other Text:
(Examples or comments displayed on slide, if any).

SQL> connect dave/dave@class2
Connected.
SQL> grant select on t to scott;

Grant succeeded.
SQL> grant all on t to tableman;

Grant succeeded.

Instructor's Notes

(If applicable)

The insert object-level privilege gives a user the privilege to execute an INSERT statement on a table they do not own.
The update object-level privilege gives a user the privilege to execute an UPDATE statement on a table they do not own.
As this example demonstrates, both the insert and update privilege can be restricted to a subset of the table’s columns. Note that when granting insert privilege on a subset of columns you must include all not null (and primary key) columns.

Other Text:
(Examples or comments displayed on slide, if any).

Include all not null columns
SQL> grant insert (c1, c3) on t to scott;

Grant succeeded.

SQL> grant update (c1, c3) on t to scott;

Grant succeeded.

SQL> desc t
Name Null? Type
-------------------------- -------- -------
C1 NOT NULL NUMBER
C2 NUMBER
C3 NUMBER

Instructor's Notes

(If applicable)

Use the WITH GRANT OPTION when granting an object-level privilege if you want the recipient to be able to pass that privilege to someone else.
Note that revoking an OBJECT-level privilege cascades. This means that if user1 executes:
GRANT SELECT ON user1.table1 TO user2 WITH GRANT OPTION
user2 then executes:
GRANT SELECT ON user1.table1 TO user3
user1 then executes:
REVOKE SELECT ON user1.table1 FROM user2
then user3 also loses the SELECT privilege. By comparison, note that revoking a SYSTEM-level privilege (such as CREATE TABLE) does not cascade.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> grant all on t to tableman with grant option;

Grant succeeded.

Instructor's Notes

(If applicable)

Like system-level privileges, taking away privileges is done with the REVOKE statement.
In this example, I first ask the question “who’s got any privileges on my “T” table?”. The answer to this question comes from the USER_TAB_PRIVS_MADE data dictionary view. This view records “all grants on objects owned by the user.” (from the Oracle9i Database Reference). Then, it is a simple matter of removing all grants with the REVOKE ALL statement.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> select distinct grantee from user_tab_privs_made
2 where table_name = 'T';

GRANTEE
------------------------------
SCOTT
TABLEMAN

SQL> revoke all on t from scott, tableman;

Revoke succeeded.

Instructor's Notes

(If applicable)

There are several data dictionary views that reveal what object-level privileges have been granted directly to a user, by a user, or even simply involve one of the user’s objects. These are:

DBA_TAB_PRIVS – contains a row for every grant issued on every table in the database.
USER_TAB_PRIVS records the object-level privileges that have been either granted or received by the user, or involve the user’s object.
USER_COL_PRIVS shows the column object-level privileges that have been granted or received by the user, or involve the user’s object. (INSERT, UPDATE and REFERENCES can be specified at the column level).

Refer to the Oracle9i Database Reference for complete descriptions of these and all data dictionary views.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> select grantee , privilege from dba_tab_privs
2 where owner = 'DAVE' and table_name = 'T';

GRANTEE PRIVILEGE
------------------------------ ---------------------
SYSTEM ALTER
SYSTEM DELETE
. . .
TABLEMAN DEBUG
TABLEMAN FLASHBACK

Instructor's Notes

(If applicable)

Object Privileges Workshop
1. Logon to the TABLEMAN user you created in the previous workshop in this lesson.
2. Grant all privileges on your “T” table to user SYSTEM (or the DBA user you created for this class).
3. Grant select and insert privileges on your “T” table to user SCOTT.
4. Query the data dictionary to determine all recipients of object privileges on your table.
5. Connect to user SCOTT and query the data dictionary to view all table privileges on objects owned by TABLEMAN.
6. Re-connect to TABLEMAN and revoke all privileges on your “T” table.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

A role is a collection of privileges. Once an Oracle user has been granted the role, all the privileges inherent in the role are automatically available to the user. (Exception: When a user has a DEFAULT ROLE, then all other roles granted must be explicitly enabled. More on default roles later in this lesson.)
Creating a role is done with the CREATE ROLE statement, which requires the CREATE ROLE privilege. The role is implicitly granted to the creator (query the DBA_ROLE_PRIVS view to see what users have been granted a role).
The ROLE name must be unique in the database and cannot be the same as an existing user or role name.
All roles belong to SYS. That is why there is no USER_ROLES data dictionary view. Instead, all roles are listed in the DBA_ROLES data dictionary view.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> create role class;

Role created.

SQL> grant select on t to class;

Grant succeeded.
Requires CREATE ROLE privilege
Role name must be unique in database

SQL> grant create procedure to class;

Grant succeeded.

Instructor's Notes

(If applicable)

Oracle database installation scripts create several roles (I am calling them “pre-defined” roles). These include roles for full database exports and imports. Refer to the Oracle9i Administration Guide, Chapter 25, for a complete list of roles and the privileges granted to these roles.
It also includes DBA, CONNECT and RESOURCE roles, though the Oracle9i Administration manual states that “The previous three roles are provided to maintain compatibility with previous versions of Oracle and may not be created automatically in future versions of Oracle. Oracle Corporation recommends that you design your own roles for database security, rather than relying on these roles.”

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

Just like granting a privilege to a user, use the GRANT statement to grant privileges to a role. The user acquires all privileges within the role upon next logon. (However, new privileges added to a role while a user is connected are immediately available to the user.)
The example above demonstrates a use of the USER_ROLE_PRIVS dictionary view. This view shows all roles granted to the current user.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> create role class;

Role created.

SQL> grant select on t to class;

Grant succeeded.
SQL> grant class to tableman;

Grant succeeded.

SQL> connect tableman/tableman@class2
Connected.

SQL> select granted_role
2 from user_role_privs;

GRANTED_ROLE
------------------------------
CLASS
Show roles granted to current user

Instructor's Notes

(If applicable)

The creator of the role is, by default, a role administrators. A role administrator can grant, revoke, alter and drop the role. Additional role administrators can be created by adding the WITH ADMIN OPTION clause when granting the role.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> connect dave/dave@class2
Connected.

SQL> grant class to tableman with admin option;

Grant succeeded.

SQL> connect tableman/tableman@class2
Connected.

SQL> grant class to scott;

Grant succeeded.

Instructor's Notes

(If applicable)

A role can be granted to another role. The recipient role (“grantee”) inherits all privileges granted to the grantor role. After the next logon, a user who has been granted the role can use the added privileges.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> grant class to connect;

Grant succeeded.

SQL> create user newuser identified by newuser;

User created.

SQL> grant connect to newuser;

Grant succeeded.

SQL> connect newuser/newuser@class2
Connected.
SQL> select count(*) from dave.t;

COUNT(*)
----------
1
Grant role CLASS to role CONNECT

Instructor's Notes

(If applicable)

You have learned that a role is a collection of privileges. One of the logical questions then, is, “what privileges does the role contain?” This query answers that question. It queries the following dictionary views:

ROLE_SYS_PRIVS - All system privileges granted to a role.
ROLE_TAB_PRIVS - Table privileges granted to a role.
ROLE_ROLE_PRIVS - All roles granted to a role.

This query is supplied to you in the file ROLE_PRIVILEGES.SQL.
Optional Mini-Workshop
- Use the supplied script ROLE_PRIVILEGES.SQL to find all privileges granted to the DBA role.

Other Text:
(Examples or comments displayed on slide, if any).

What privileges does a role contain?

SQL> select 'System Privilege' as priv_type,
2 privilege,
3 ' ' as table_owner, ' ' as table_name
4 from role_sys_privs
5 where role = 'CONNECT'
6 union all
7 select 'Table Privilege', privilege, owner, table_name
8 from role_tab_privs
9 where role = 'CONNECT'
10 union all
11 select 'Role', granted_role, ' ', ' '
12 from role_role_privs
13 where role = 'CONNECT';

PRIV_TYPE PRIVILEGE TAB
---------------- ---------------------------------------- ---
System Privilege CREATE VIEW
System Privilege CREATE TABLE
. . .

Do the mini-workshop

Instructor's Notes

(If applicable)

PL/SQL procedures (e.g. CREATE PROCEDURE) do not recognize privileges granted via roles. PL/SQL programs require direct grants.
The example shown above illustrates this point. The user successfully queries DAVE.T from the SQL*Plus command prompt. However, procedure “X” fails to compile because table “DAVE.T” does not exist (of course, it does exist, it is just that the PL/SQL engine does not recognize the SELECT privilege from the role).
Author’s Note
- This demonstration requires the following statements:
- create user newuser identified by newuser;
- create role class;
- grant select on dave.t to class;
- grant create procedure to class;
- grant class to connect;
- grant connect to newuser;

Other Text:
(Examples or comments displayed on slide, if any).

SQL> select * from dave.t;
C1 C2 C3
---------- ---------- ----------
1 1 1

SQL> create procedure x as
2 begin
3 for x in (select * from dave.t) loop
4 null;
5 end loop;
6 end;
7 /

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE X:

LINE/COL ERROR
-------- -----------------------------------------------
3/13 PL/SQL: SQL Statement ignored
3/32 PL/SQL: ORA-00942: table or view does not exist

Instructor's Notes

(If applicable)

Use the REVOKE statement to revoke a role from a user, another role or PUBLIC. Note that a connected user continues to have the role granted - and thus can use the role privileges - until the user disconnects.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> revoke class from newuser;

Revoke succeeded.

Instructor's Notes

(If applicable)

Use the DROP ROLE statement to remove a role from the database. You will need one of three things to have the privilege to do this:

Creator of the role
Administrator of the role (granted the role WITH ADMIN OPTION)
Have the DROP ANY ROLE system privilege.

DROP ROLE will immediately revoke all privileges from all users who have been granted the role – you do not need to REVOKE it from the users first.
Supplemental Notes
- You may want to check the impact of the drop first. This can be done by querying DBA_ROLE_PRIVS (roles granted to users and roles):
- SQL> select grantee from dba_role_privs
- 2 where granted_role = 'CLASS';
- GRANTEE
- ------------------------------
- DAVE
- NEWUSER

Other Text:
(Examples or comments displayed on slide, if any).

SQL> drop role class;

Role dropped.

Instructor's Notes

(If applicable)

You have seen many dictionary views used in this chapter. Here is a helpful summary of data dictionary views related to ROLES:

DBA_ROLES - All defined roles.
DBA_ROLE_PRIVS – All roles granted to users and roles.
ROLE_SYS_PRIVS - All system privileges granted to roles.
ROLE_TAB_PRIVS - Table privileges granted to roles.
ROLE_ROLE_PRIVS - All roles granted to roles.
USER_ROLE_PRIVS - Roles granted to current user.
SESSION_ROLES - Roles which the user currently has enabled. Roles can be enabled/disabled with the SET ROLE statement or by assigning DEFAULT roles.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> select * from session_roles;

ROLE
------------------------------
CLASS
USER_ROLE_PRIVS
SESSION_ROLES

Instructor's Notes

(If applicable)

There are a few more concepts related to roles that you should be aware of. (They are not as commonly used features, so I have grouped them here as “miscellaneous” topics.)
Users can have a default role. A default role is the only active role upon connection to the database. This is done with CREATE USER or ALTER USER DEFAULT ROLE as shown above. Any other roles the user has must be explicitly enabled with the SET ROLE statement.
Users can enable roles with the SET ROLE statement. A session has all privileges from active (enabled) roles (and any direct grants). Query SESSION_ROLES to see active roles. All roles not named in the SET ROLE statement are disabled. SET ROLE supports the following “grouping”-type options

ALL - Activate all granted roles.
NONE - Turn off (deactivate) all granted roles.
EXCEPT - Exclude the following from the previous ALL or NONE action.

Notes for this slide continue on the next page…

Other Text:
(Examples or comments displayed on slide, if any).

SQL> alter user newuser default role connect;

User altered.

SQL> set role dba;

Role set.

SQL> alter role dba identified by beatles;

Role altered.

SQL> set role dba identified by beatles;

Role set.

Instructor's Notes

(If applicable)

Notes continued from the previous page:
A role can be assigned a password. To enable a password protected role, you need to know the password. To remove a password, use:
SQL> alter role dba not identified;

Role altered.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

Roles Workshop
1. Create a role called ‘MANAGER' and assign that role the ability to be able to create tables and synonyms. Create another role called ‘SALESPERSON' which is protected by a password. The ‘SALESPERSON' role should be given the ability to be able to make connections to the database.
2. Give the ‘MANAGER' role the ability to be able to insert, update rows on the U1.delegates table whereas the ‘SALESPERSON' role can only query them.
3. Assign the ‘SALESPERSON' role to the ‘MANAGER' role since the manager will require all the privileges given to the ‘SALESPERSON' role.
4. Give both roles to the U3 Oracle user (created in the last exercise).
5. Connect as the U3 user and determine which roles are present. Access the U1.delegates table and determine what actions (insert, update, delete) are allowed.
6. Enable only the ‘SALESPERSON' role and again determine which actions are allowed in the U3 user.

Other Text:
(Examples or comments displayed on slide, if any).