//
you're reading...
SQL

A Short Guide to Oracle SQL [IX] – User management

DCL Statements

Data Control Language (DCL) used to control access to the database and the data reside in it. It enforce data security.

DCL statements preserve the security of data within the database and ensure that no one but the authorised user is able to view or change data. GRANT statement is used to provide data access authorization and REVOKE used to removing privilege(s) previously granted to the user.

USER

In an typical SQL environment, a user is identified by a valid user name (user identifier) and password. The process in which database verifies the credential provided by the user and allows the access of the database to the user is known as “Authentication“. Oracle (and some other databases) also permits the use of user identifiers from the underlying OS.

Each Oracle database contains a number of defaults account, which also includes administrative accounts (SYS and SYSTEM etc). Other users can be created by using CREATE USER statement.

CREATE USER user
   IDENTIFIED BY password
   [ DEFAULT TABLESPACE tablespace
    | TEMPORARY TABLESPACE
        { tablespace | tablespace_group_name }
    | { QUOTA { size_clause | UNLIMITED } ON tablespace }...
    | PROFILE profile
    | PASSWORD EXPIRE
    | ACCOUNT { LOCK | UNLOCK }
   ] ;

For detailed CREATE USER statement please refer to ORACLE SQL Language Reference.

CREATE USER mprakash                    -- name of the user
IDENTIFIED BY hil43xol                  -- specify password for the user. case-sensitive starting with 11g release
DEFAULT TABLESPACE users                -- default tablespace for user's objects. 
TEMPORARY TABLESPACE temp               -- default tablespace for temporary segements. 
QUOTA 10M ON users                      -- amount of space user can be used in the tablespace
PASSWORD EXPIRE;                        -- forces user to change password on the first login


dbadmin@O11R2> conn mprakash
Enter password:
ERROR:
ORA-28001: the password has expired

Changing password for mprakash
New password:
Retype new password:
ERROR:
ORA-01045: user MPRAKASH lacks CREATE SESSION privilege; logon denied

Password changed
Warning: You are no longer connected to ORACLE.
dbadmin@O11R2>

NOTE:

    • Assigning default tablespace to the user doesn’t allow (a user) to store objects in that tablespace. You need to use QUOTA clause to specify the space amount for user that he/she can use.
    • To change the characteristics of user use ALTER USER and to drop user use DROP USER.
    • ALTER USER user_name IDENTIFIED BY password [PASSWORD EXPIRE];
      
      DROP USER user_name [CASCADE];
      
    • CASCADE drops all objects in the user’s schema before dropping the user.

GRANT

Just creating a user account doesn’t mean that he/she can establish a connection/session with database or can create database objects, modify it or delete it. A user at least require to have a minimum of CREATE SESSION privilege to connect to the database and establish a session.

Privileges allow a user to run specific SQL statements within the database. There are two types of privileges in Oracle database –

System Privileges

Allow a user to establish a session against the database. It also allow a user to perform DDL operations.

GRANT sys_privilege [, sys_privilege...] 
TO username|role [, username|role...] 
[WITH ADMIN OPTIOn];
    • To grant a system privilege one must have GRANT ANY PRIVILEGE.
    • WITH ADMIN OPTION clause allow a grantee to grant a privilege to another user.
    • dbadmin@O11R2>GRANT CREATE SESSION, CREATE TABLE TO mprakash;
      Grant succeeded.
      
      dbadmin@O11R2>GRANT EXECUTE ANY PROCEDURE TO mprakash WITH ADMIN OPTION;
      Grant succeeded.
      
      dbadmin@O11R2> conn mprakash
      Enter password:
      Connected.
      
      mprakash@O11R2> GRANT EXECUTE ANY PROCEDURE TO scott;
      Grant succeeded.
      
    • USER_SYS_PRIVS data dictionary view can be used to see the system privileges granted to the user.
    • mprakash@O11R2> desc user_sys_privs;
       Name                                                  Null?    Type
       ----------------------------------------------------- -------- ------------------------------------
       USERNAME                                                       VARCHAR2(30)
       PRIVILEGE                                             NOT NULL VARCHAR2(40)
       ADMIN_OPTION                                                   VARCHAR2(3)
      
      mprakash@O11R2> SELECT * FROM user_sys_privs;
      
      USERNAME                       PRIVILEGE                                ADM
      ------------------------------ ---------------------------------------- ---
      MPRAKASH                       CREATE SESSION                           NO
      MPRAKASH                       CREATE TABLE                             NO
      MPRAKASH                       EXECUTE ANY PROCEDURE                    YES
      

Objects Privileges

Allow user to perform (specified) actions on database objects for example query from the emp table in the scott schema or executing a stored procedure.

GRANT {objectprivilege|ALL} [(columnname),
  objectprivilege (columnname)]
 ON objectname
 TO {username|rolename|PUBLIC}
 [WITH GRANT OPTION];

To grant a object privilege you must be the owner of the object or the object’s owner must have granted you the object privileges with WITH GRANT OPTION.

dbadmin@O11R2> GRANT SELECT ON scott.emp TO mprakash WITH GRANT OPTION;
Grant succeeded.

dbadmin@O11R2> GRANT SELECT, UPDATE (salary, commission_pct ,job_id) ON hr.employees TO mprakash;
Grant succeeded.

dbadmin@O11R2> GRANT ALL ON hr.departments TO mprakash;
Grant succeeded.

dbadmin@O11R2> conn mprakash
Enter password:
Connected.

mprakash@O11R2> SELECT employee_id, last_name, salary, manager_id, department_id
     FROM hr.employees
     WHERE employee_id = 181;

EMPLOYEE_ID LAST_NAME                     SALARY MANAGER_ID DEPARTMENT_ID
----------- ------------------------- ---------- ---------- -------------
        181 Fleaur                          3100        120            50

mprakash@O11R2> UPDATE hr.employees
     SET salary = 6700,
         manager_id = 108,
         department_id = 100
     WHERE employee_id = 181;

UPDATE hr.employees
          *
ERROR at line 1:
ORA-01031: insufficient privileges

mprakash@O11R2> UPDATE hr.employees
     SET salary = 6700
     WHERE employee_id = 181;

1 row updated.

mprakash@O11R2> SELECT grantee, owner, table_name, grantor, privilege
     FROM user_tab_privs;

GRANTEE       OWNER  TABLE_NAME      GRANTOR                        PRIVILEGE
------------- ------ --------------- ------------------------------ --------------------
MPRAKASH      SCOTT  EMP             SCOTT                          SELECT
MPRAKASH      HR     EMPLOYEES       HR                             SELECT
MPRAKASH      HR     DEPARTMENTS     HR                             ALTER
MPRAKASH      HR     DEPARTMENTS     HR                             DELETE
MPRAKASH      HR     DEPARTMENTS     HR                             INDEX
MPRAKASH      HR     DEPARTMENTS     HR                             INSERT
MPRAKASH      HR     DEPARTMENTS     HR                             SELECT
MPRAKASH      HR     DEPARTMENTS     HR                             UPDATE
MPRAKASH      HR     DEPARTMENTS     HR                             REFERENCES
MPRAKASH      HR     DEPARTMENTS     HR                             ON COMMIT REFRESH
MPRAKASH      HR     DEPARTMENTS     HR                             QUERY REWRITE
MPRAKASH      HR     DEPARTMENTS     HR                             DEBUG
MPRAKASH      HR     DEPARTMENTS     HR                             FLASHBACK

13 rows selected.

It would be better to group the privileges into ROLES and then assign it to the user rather than assigning them individually. CREATE ROLE is used for creating role. Some pre-defined roles are CONNECT, RESOURCE, DBA.

dbadmin@O11R2> CREATE ROLE essentials;
Role created.

dbadmin@O11R2> GRANT CREATE SESSIOn, CREATE TABLE TO essentials;
Grant succeeded.

dbadmin@O11R2> conn db_user
Enter password:
ERROR:
ORA-01045: user DB_USER lacks CREATE SESSION privilege; logon denied

Warning: You are no longer connected to ORACLE.

dbadmin@O11R2> conn dbadmin
Enter password:
Connected.

dbadmin@O11R2> GRANT essentials TO db_user;
Grant succeeded.

dbadmin@O11R2> conn db_user
Enter password:
Connected.

REVOKE

REVOKE statement used to revoke system privileges, roles and object privileges from users and roles.
Revoking an object privilege

REVOKE sys_privilege[, <sys_privilege>...]}
FROM <grantee> [, <grantee>...];
mprakash@O11R2> GRANT SELECT ON scott.emp TO db_user;       -- user mprakash has SELECT privilege on
Grant succeeded.                                            --   scott.emp WITH GRANT OPTION


dbadmin@O11R2> REVOKE SELECT ON scott.emp FROM mprakash;
Revoke succeeded.

mprakash@O11R2> SELECT empno, ename, sal 
            FROM scott.emp;
FROM scott.emp
                                    *
ERROR at line 2:
ORA-00942: table or view does not exist

mprakash@O11R2> conn db_user
Enter password:
Connected.

tarzan@O11R2> select empno, ename, sal FROM scott.emp;
select empno, ename, sal FROM scott.emp
                                    *
ERROR at line 1:
ORA-01031: insufficient privileges

In the following example user mprakash has the ability to give SELECT privilege on scott.emp to other users. He granted the privilege to db_user. Now dbadmin (who originally granted SELECT privilege with GRANT OPTION to user mprakash) revoke the SELECT privilege from user mprakash. This results in user mprakash and db_user to lose SELECT privilege on scott.emp.

Revoking a system privilege

REVOKE object_privilege>[, <object_privilege>...]}
ON <database object>
FROM <grantee> [, <grantee>...];
dbadmin@O11R2> REVOKE CREATE TABLE FROM mprakash;
Revoke succeeded.

dbadmin@O11R2> CONN mprakash
Enter password:
Connected.

mprakash@O11R2> CREATE TABLE test
     (test_id VARCHAR2(4),
     test_name VARCHAR2(20));
CREATE TABLE test
*
ERROR at line 1:
ORA-01031: insufficient privileges

Revoking system privilege does not result in cascade. Suppose dbadmin grant CREATE TABLE privilege to user mprakash WITH ADMIN OPTION and mpraksh grants the CREATE TABLE privilege to db_user. Now, if dbadmin revoke the CREATE TABLE privilege from mprakash, db_user still has CREATE TABLE system privilege.

dbadmin@O11R2> GRANT CREATE TABLE TO mprakash WITH ADMIN OPTION;
Grant succeeded.

mprakash@O11R2> GRANT CREATE TABLE TO test;
Grant succeeded.

dbadmin@O11R2> REVOKE CREATE TABLE FROM mprakash;
Revoke succeeded.

mprakash@O11R2> CREATE TABLE test
         (test_id VARCHAR2(4),
         test_name VARCHAR2(20));
CREATE TABLE test
*
ERROR at line 1:
ORA-01031: insufficient privileges

test@O11R2> CREATE TABLE test
        (test_id VARCHAR2(4),
         test_name VARCHAR2(20));

Table created.

test@O11R2> select * from user_sys_privs;

USERNAME                       PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
TEST                           CREATE TABLE                             NO
TEST                           CREATE SESSION                           NO
Advertisements

Discussion

Trackbacks/Pingbacks

  1. Pingback: A Short Guide to Oracle SQL [I] – RDBMS Basic Concepts | musingdba - April 16, 2013

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: