User access privileges.

Managing user accounts.

Creating user account.

CREATE USER user_name
  IDENTIFIED { BY password | EXTERNALLY | GLOBALLY AS 'external_name'}
 [ DEFAULT TABLESPACE tablespace_name ]
 [ TEMPORARY TABLESPACE tablespace_name ]
 [ QUOTA { integer [K|M] | UNLIMITED } ON tablespace_name ]
 [ PROFILE profile_name ]
 [ PASSWORD EXPIRE ]
 [ ACCOUNT { LOCK | UNLOCK } ];

• Database authentication. This is the default option. While creating a user we can provide her or his password by using IDENTIFIED BY clause. The password should not be included in single quotation marks.
• External authentication. While using this type of authentication, Oracle only checks that the user is a legitimate user for that database; the password is validated by the operating system or network. Note: this method of authentication is not recommended to use on OS with security issues.
• Global authentication. Oracle only checks that the user is a valid user for the database. The password is validated by the Oracle Security Service, a separately licensed and configured service.

Here is an example

CREATE USER bob
 IDENTIFIED BY secret_word
 PASSOWRD EXPIRE
 DEFAULT TABLESPACE student_space
 QUOTA 10M ON student_space
 DEFAULT PROFILE student;

Modifying user account.

ALTER USER user_name
 [ IDENTIFIED { BY password [REPLACE old_passwd] | EXTERNALLY | GLOBALLY AS 'external_name'} ]
 [ DEFAULT TABLESPACE tablespace_name ]
 [ TEMPORARY TABLESPACE tablespace_name ]
 [ QUOTA { integer [K|M] | UNLIMITED } ON tablespace_name ]
 [ PROFILE profile_name ]
 [ PASSWORD EXPIRE ]
 [ ACCOUNT { LOCK | UNLOCK } ]
 [ DEFAULT ROLE { role_list | NONE | ALL [EXCEPT role_list] } ];

ALTER USER bob
 IDENTIFIED BY password REPLACE secret_word
 DEAFULT ROLE student_role;

ALTER USER bob ACCOUNT LOCK;

Removing user account.

DROP USER user_name [ CASCADE ];

• If the user's schema contains tables, then Oracle drops the tables and automatically drops any referential integrity constraints on tables in other schemas that refer to primary and unique keys on these tables.
• If this clause results in tables being dropped, then Oracle also drops all domain indexes created on columns of those tables and invokes appropriate drop routines.

Profile settings.

Creating a profile

CREATE PROFILE profile_name LIMIT
 [ resource_parameter { value | UNLIMITED | DEFAULT } ]
 ...
 [ resource_parameter { value | UNLIMITED | DEFAULT } ]
 [ password_parameter { value | UNLIMITED | DEFAULT } ]
 ...
 [ password_parameter { value | UNLIMITED | DEFAULT } ];

CREATE PROFILE student LIMIT
  CONNECT_TIME 300
  IDLE_TIME 60
  SESSIONS_PER_USER 5;

Modifying profiles.

ALTER PROFILE profile_name LIMIT
 [ resource_parameter { value | UNLIMITED | DEFAULT } ]
 ...
 [ resource_parameter { value | UNLIMITED | DEFAULT } ]
 [ password_parameter { value | UNLIMITED | DEFAULT } ]
 ...
 [ password_parameter { value | UNLIMITED | DEFAULT } ];

ALTER PROFILE student LIMIT

Removing a profile.

DROP PROFILE profile_name [ CASCADE ];

Privileges and roles.

Object privileges.

GRANT { privilege_list | ALL [PRIVILEGES] | role_list }
   ON object_list
   TO { user_list | role_list | PUBLIC }
   [ WITH GRANT OPTION ] ;

GRANT SELECT
   ON ist466.employees
   TO student;
GRANT INSERT, SELECT
   ON system.objects_created
   TO bob, bill
   WITH GRANT OPTION;

In the case of granting DELETE, INSERT, or UPDATE privileges we may use the list of columns we would like to grant these privileges on. These columns should be specified in parentheses after the privilege name. For example

GRANT UPDATE (grade)
   ON ist.my_records
   TO PUBLIC;

To revoke a privilege, we need to use the REVOKE statement. The syntax of the statement is

REVOKE { privilege_list | ALL | role_list }
    ON object_list
  FROM { user_list | role_list | PUBLIC }
  [ CASCADE CONSTRAINTS ] [ FORCE ];

Specify FORCE to revoke the EXECUTE object privilege on user-defined type objects with table or type dependencies. We must use FORCE to revoke the EXECUTE object privilege on user-defined type objects with table dependencies.
If we specify FORCE, then all privileges will be revoked, but all dependent objects are marked INVALID, data in dependent tables becomes inaccessible, and all dependent function-based indexes are marked UNUSABLE. (Re-granting the necessary type privilege will re-validate the table.)

Please note several things about revoking object privileges:

• we can revoke only the privilege we granted (same true for system privileges);
• if user A granted a privilege to user B with the WITH GRANT OPTION, and user B granted the same privilege to user C, then after revoking this privilege from user B, user C looses it as well;
• the same thing is true if we drop user B. When we drop a user, all objects privileges granted by the user are lost;
• we cannot revoke only the ability to grant the privilege further, we have to revoke the whole privilege and re-grant without the WITH GRANT OPTION;
• we cannot revoke the DELETE, INSERT, or UPDATE privileges on specific columns, we have to revoke the entire privilege.

REVOKE SELECT
    ON ist.employees
  FROM bob;

System privileges.

To grant system privilege we use the same GRANT statement, but with slightly different syntax

GRANT { privilege_list | ALL [PRIVILEGES] | role_list }
   ON object_list
   TO { user_list | role_list | PUBLIC }
   [ IDENTIFIED BY password ]
   [ WITH ADMIN OPTION ] ;

Unlike object privileges, the grantor of the system privileges or role privileges is not kept; that is, if user A granted a system privilege to user B with the WITH ADMIN OPTION, and user B granted it further to user C, then if user B is dropped or the privilege was revoked from her/him, then user C still retains the privilege.

To revoke a system privilege we use the same REVOKE statement as we do for object privileges, but for system privileges we cannot use the CASCADE CONTRAINTS clause. Thus, the syntax of revoking system privileges is

REVOKE { privilege_list | ALL | role_list }
    ON object_list
  FROM { user_list | role_list | PUBLIC };

Roles.

To create a role, we need to use the CREATE ROLE statement. The syntax for the statement is

CREATE ROLE role_name
 [ IDENTIFIED BY { password | EXTERNALLY | GLOBALLY } ];

CREATE ROLE ist466_role;
GRANT ALTER SESSION,
      CREATE SEQUENCE,
      CREATE SESSION,
      CREATE SYNONYM,
      CREATE TABLE,
      CREATE VIEW
   TO ist466_role;
grant ist466_role TO dementiev;

SET ROLE
  { role_name [IDETIFIED BY password] | ALL [EXCEPT role_list] | NONE };

Oracle provides some predefined roles. The three most important roles are

Oracle predefined roles

Role	Privileges granted
CONNECT	ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE VIEW
RESOURCE	CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER, CREATE TYPE
DBA	This role has 124 system privileges granted to let the user full administrative privilege over the database.

To retrieve all roles predefined in the database and privileges granted to these roles use the dictionary views ROLE_SYS_PRIVS and ROLE_TAB_PRIVS. For example, to print all system privileges granted to the predefined role DBA we can execute

SELECT privilege
  FROM role_sys_privs
 WHERE role = 'DBA';

Now, to create a valid user who can connect to Oracle database and be able to create her/his own tables we can execute the commands:

CREATE USER student IDENTIFIED BY psswrd;
GRANT CONNECT TO student;

Oracle dictionary about privileges.