Oracle Create User , System Privileges and Oracle Object Privileges



Last updated on April 22nd, 2017 at 01:06 pm

We will be posting information on User access and security in oracle database.This post will give good information on  Oracle Create User  , System Privileges and Oracle Object Privileges,How to show all privileges from a user in oracle

Oracle Object Privileges

 

 

CREATE USER statement

It is employed to create a user (schema).

The default authentication for a new user is the database. The user is assigned password, and the password is kept encrypted in the database.  There are two other type of authentication possible, first one is the external authentication using the operating system level. External authentication checks the password in operating system level.

The other one is the Global authentication which checks the user password in Oracle security service or other third party directory service.

 

Using database authentication CREATE USER SCOTT IDENTIFIED BY CAT;

 

It will create a user that will be identified by the operating system

. The OS_AUTHENT_PREFIX=OPS$ must be set in the init.ora file.

 

CREATE USER OPS$SCOTT IDENTIFIED EXTERNALLY;

 

Global Authentication CREATE USER SCOTT IDENTIFIED GLOBALLY AS ‘CN=SCOTT, OU=SHR, O=TEST, C=US’;

 

 

Assigning Attributes

We can change the user attributes using the ALTER USER. We can either alter them or assigned them

ALTER USER SCOTT IDENTIFIED BY <password>; It  will change SCOTT password to password specified

 

ALTER USER <user_name> PASSWORD EXPIRE. It will expire password  and ask the user to change it
ALTER USER SCOTT DEFAULT TABLESPACE USERS; It will assign the default tablespace USERS to SCOTT.

 

ALTER USER SCOTT TEMPORARY TABLESPACE TEMP; It will assign the temporary tablespace TEMP to SCOTT.

 

ALTER USER <user_name> QUOTA <n/UNLIMITED> ON <tablespace_name>; where n is a number (including 0).

 

The sql  will assign the Tablespace quota to the user
ALTER USER <user_name> ACCOUNT LOCK/UNLOCK;

 

This is to control the account
ALTER USER <user_name> PROFILE <profile_name>;

 

Fisrt  of all what is Profiles

A profile is used to force limitations on resources. Some of the limitations include CPU time, connection time, password parameters, failed login attempts, etc.  The given sql will  help assigning a profiles to the user

 

 

Create user statement with some attributes set will be

 

CREATE USER SCOTT

IDENTIFIED BY TTOC

DEFAULT TABLESPACE USERS

QUOTA 10M ON USERS

TEMPORARY TABLESPACE temp

QUOTA 5M ON system

PROFILE application_user

PASSWORD EXPIRE;

 

Oracle System Privileges

It allows the grantee to create, alter, drop and manage database objects.

Oracle has more than 100 system privileges (found in the SYSTEM_PRIVILEGE_MAP table).

CLUSTER CREATE/CREATE ANY/ALTER ANY/DROP ANY CLUSTER to manage clusters
DATABASE ALTER DATABASE, ALTER SYSTEM & AUDIT SYSTEM,
INDEX CREATE ANY/ALTER ANY/DROP ANY INDEX
PROFILE CREATE/ALTER/DROP PROFILE to manage profiles
ROLE CREATE/ALTER ANY/DROP ANY/GRANT ANY (allows REVOKE) ROLE to manage roles

 

Rollback Segment CREATE/ALTER/DROP ROLLBACK SEGMENT to manage rollback segments

 

USER CREATE/ALTER/BECOME/DROP USER to manage user privileges

 

VIEW CREATE/CREATE ANY/DROP ANY VIEW
SYNONYM CREATE/CREATE ANY/CREATE PUBLIC/DROP ANY/DROP PUBLIC SYNONYM to manage synonyms

 

SESSION CREATE/ALTER/RESTRICTED1 SESSION & ALTER RESOURCE COST to manage session privileges

 

TABLE CREATE/CREATE ANY/ALTER ANY/DROP ANY/SELECT ANY/INSERT ANY/UDPATE ANY/DELTE ANY/LOCK ANY TABLE
TABLESPACE CREATE/ALTER/DROP/MANAGE

 

 

There are 2 special privileges in an Oracle database called SYSDBA and SYSOPER. Both privileges allow database operations such as STARTUP, SHUTDOWN, OPEN, MOUNT, BACKUP, ARCHIVELOG and RECOVER. SYSDBA allows the CREATE DATABASE command and CHANGE CHARACTER SET options.

 

The system privileges are provided by Database administrator to the users using grant command and revoked using revoke command

If the DBA need to provide system privilege to some body so that other person can also grant it, then it need to be given with admin option

 

GRANT create session   TO user;

GRANT create session   TO user with admin option;

Revoke create session   from user;

 

User System privileges

When the user is created, we generally grant the below system privilege’s

    • CREATE SESSION
    • CREATE TABLE
    • CREATE VIEW
    • CREATE SEQUENCE
    • CREATE PROCEDURE

With this user can start creating the table and other objects.

Oracle Object Privileges

These are granted on database objects (such as table, view, procedure etc.…)

ALTER allow grantee to alter object
DELETE allow grantee to execute DELETE statement on object (table/view)
EXECUTE allow grantee to execute a stored procedure/function
INDEX allow grantee to create an index or lock a specific table (must lock table in order to create index),
INSERT allow grantee to insert data into table/view
REFERENCE allow grantee to create a referential integrity constraint
ALL allow grantee all privileges on object.
SELECT Allow user to select the data
UPDATE Allow user to update the data

oracle_privs

The owner has all the privilege’s and an owner can give specific privilege’s on the objects

The owner can provide the privileges using the below command

GRANT privileges ON object TO user;

The privileges could be select, insert, update, delete or ALL

If the owner wants to provide privilege to all the users

 

GRANT privileges ON object TO public;

If the owner wants to provide privilege to the user with grant option. Basically that means, the grantee can further assign this privilege to others

 

GRANT privileges ON object TO user with grant option;

Similarly, we have the revoke command to remove the privileges from the objects

 

What is a oracle Role?

A role is a database object used to enforce privileges. A user can be assigned a role in order to set privileges on database objects:

GRANT EBS_ADMIN TO SCOTT; will grant the EBS_ADMIN role with all its privileges to user SCOTT.

 

Creating and Using Roles

The CREATE ROLE [IDENTIFIED BY <password>] command, creates a role. Assigning privileges to the role is done using the GRANT command. Revoking privileges is done using the REVOKE <privilege> FROM ROLE.

What is the difference between system and object privileges?

System Level Privileges Object level privileges
System privileges are privileges given to users to allow them to perform certain functions that deal with managing the database and the server.

Oracle has more than 100 system privileges (found in the SYSTEM_PRIVILEGE_MAP table).

Example:

Create user ,create table

Object privileges are privileges given to users so that they can perform certain actions upon certain database objects. These are granted on database objects (such as table, view, procedure etc.…)

 

Example select,delete,update

Data Dictionary Tables and Views

Oracle has provided data dictionary views to get the information about privileges

There are three categories of views

USER_% This view contain information of the objects owned by the user only

Example

USER_TABLES, USER_TAB_COLS

ALL-% This view contains information of the objects which the user can access in the database.

Example

ALL_TABLES,ALL_TAB_COLS

DBA_% This view contains information of the all objects   in the system and these are restricted views which are accessible to the user who have DBA role

Example

DBA_TABLES,DBA_TAB_COLS

 

Checking Privileges Views

USER_ROLE_PRIVS Roles accessible by the user
ROLE_SYS_PRIVS System privilege’s granted to Role
ROLE_TAB_PRIVS Table privilege’s granted to Role
USER_TAB_PRIVS_MADE Objects privileges granted on the user’s objects
USER_TAB_PRIVS_RECD Objects privileges granted to the user
USER_COL_PRIVS_MADE

 

Objects privileges granted on the columns of the user’s objects
USER_COL_PRIVS_RECD Objects privileges granted to the user on the specific column’s
USER_SYS_PRIVS Lists system privileges granted to the user
ALL_OBJECTS displays all of the objects to which the user has access to
ALL_COL_PRIVS_MADE displays all of the grants on columns that the user owns or that the user has granted
USER_OBJECTS displays only the objects owned by the user.

 

USER_TABLES displays only the tables owned by the user.
USER_VIEWS displays only the views owned by the user.
DICTIONARY this view provides descriptions of the data dictionary tables and views that are accessible to the user
TABLE_PRIVILEGES Displays the grants on objects:

    • When role or a PUBLIC is grantee
    • The user has granted.
    • That have been granted to the user.
    • That the user owns.

 

Useful queries for the privileges

How to show all privileges from a user in oracle

 

set lines 110 pages 1000 ver off

col role for a16

col pv for a75 hea ‘PRIVILEGE OR ROLE’

break on role on type skip 1

define usercheck = ‘SH’

select grantee, ‘ROL’ type, granted_role pv

from dba_role_privs where grantee = ‘&usercheck’ union

select grantee, ‘PRV’ type, privilege pv

from dba_sys_privs where grantee = ‘&usercheck’ union

select grantee, ‘OBJ’ type,

max(decode(privilege,’WRITE’,’WRITE,’))||max(decode(privilege,’READ’,’READ’))||

max(decode(privilege,’EXECUTE’,’EXECUTE’))||max(decode

(privilege,’SELECT’,’SELECT’))||

max(decode(privilege,’DELETE’,’,DELETE’))||max(decode

(privilege,’UPDATE’,’,UPDATE’))||

max(decode(privilege,’INSERT’,’,INSERT’))||’ ON ‘||object_type||’

“‘||a.owner||’.’||table_name||'”‘ pv

from dba_tab_privs a, dba_objects b

where a.owner=b.owner and a.table_name = b.object_name and

a.grantee=’&usercheck’

group by a.owner,table_name,object_type,grantee union

select username grantee, ‘—‘ type, ’empty user —‘ pv from dba_users

where not username in (select distinct grantee from dba_role_privs) and

not username in (select distinct grantee from dba_sys_privs) and

not username in (select distinct grantee from dba_tab_privs) and username like

‘%&usercheck%’

group by username

order by grantee, type, pv;

Determine the system privs given to the user

SELECT GRANTEE, PRIVILEGE FROM DBA_SYS_PRIVS

WHERE GRANTEE = ‘USER’;

 

 

Checking which table privileges are granted by you to other users.

SELECT * FROM USER_TAB_PRIVS_MADE

Checking which table privileges are granted to you by other users

SELECT * FROM USER_TAB_PRIVS_RECD;

Checking which column level privileges are granted by you to other users.

SELECT * FROM USER_COL_PRIVS_MADE;


Checking  which column level privileges are granted to you by other users

SELECT * FROM USER_COL_PRIVS_RECD;


Checking which privileges are granted to roles

SELECT * FROM USER_ROLE_PRIVS;

How to grant and revoke privileges  in Oracle

Objects grant/revoke from user

grant select on dept to public;

revoke select  on dept  from public;

Objects grant/revoke from Role

grant delete on dept to dept_role;

revoke update on dept from dept_role;

Users and Roles in Oracle database 12c

With 12c , we have a concept of container database (CDB),so things get change with respect to users. We have common user and local users

Common users are created in root container .Adding a common user involves adding a description of that user in the root and in every PDB  while local users are created in respective PDB in a traditional manner

Some restrictions for users naming in 12c are

  1. In a non-CDB, a user name cannot begin with C## or c##.
  2. In a CDB, the requirements for a user name are as follows:

In Oracle Database 12c Release 1 (12.1.0.1), the name of a common user must begin with C## or c## and the name of a local user must not begin with C## or c##.

Starting with Oracle Database 12c Release 1 (12.1.0.2):

The name of a common user must begin with characters that are a case-insensitive match to the prefix specified by the COMMON_USER_PREFIX initialization parameter. By default, the prefix is C##.

The name of a local user must not begin with characters that are a case-insensitive match to the prefix specified by the COMMON_USER_PREFIX initialization parameter. Regardless of the value of COMMON_USER_PREFIX, the name of a local user can never begin with C## or c##.

Similar is the case of Roles also. Oracle supplied roles are common roles and they are available to both root and PDB’s .  Local roles are created the same way as traditional way as pre 12c.   For creating common roles, it should start C##  and you should be connected to root container to create it

Related Links

Managing User Privileges and Roles

How to create Oracle views

how to create table in oracle

What is Sequences in oracle

Hope you like the post.Please do provide the feedback


Leave a Reply