Last updated on August 22nd, 2018 at 11:53 am
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
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
Oracle System Privileges
It allows the grantee to create, alter, drop and manage database objects.For example, the privileges to create tablespaces and to delete the rows of any table in a database are system privileges.
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|
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|
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).
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.…)
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
|ALL-%||This view contains information of the objects which the user can access in the database.
|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
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|
|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:
Useful queries for the privileges
how to check user privileges 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,’INSERT’,’,INSERT’))||’ ON ‘||object_type||’
from dba_tab_privs a, dba_objects b
where a.owner=b.owner and a.table_name = b.object_name and
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
group by username
order by grantee, type, pv;
Determine the system privileges 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
- In a non-CDB, a user name cannot begin with C## or c##.
- In a CDB, the requirements for a user name are as follows:
In Oracle Database 12c Release 1 (188.8.131.52), 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 (184.108.40.206):
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
Hope you like the post.Please do provide the feedback