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
the