How to Create User in Oracle and Grant privileges

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

How to Create User in Oracle and Assign privileges

Table of Contents

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 a password, and the password is kept encrypted in the database. There are two other types of authentication possible, first one is external authentication using the operating system level. External authentication checks the password at the operating system level.

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

Assigning Attributes –

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

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. 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).

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 the Database administrator to the users using the grant command and revoked using the revoke command

If the DBA need to provide system privilege to somebody so that another person can also grant it, then it needs 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.

Sample

CREATE USER "TEST" IDENTIFIED BY TEST
DEFAULT TABLESPACE "TOOLS"
TEMPORARY TABLESPACE "TEMP";

grant CREATE TRIGGER to TEST;
grant CREATE PROCEDURE to TEST;
grant CREATE SESSION to TEST;
grant CREATE TYPE to TEST;
grant CREATE TABLE to TEST;
grant CREATE VIEW to TEST;
grant ALTER SESSION to TEST;
grant CREATE SEQUENCE to TEST;
grant CREATE SYNONYM to TEST;
grant CREATE ANY SYNONYM to TEST;
grant UNLIMITED TABLESPACE to TEST;

Oracle Object Privileges

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

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 a 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;

It 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?

Data Dictionary Tables and Views

Oracle has provided data dictionary views to get information about privileges

There are three categories of views

Checking Privileges Views

Useful queries for the privileges

how to check user privileges in oracle

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 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;

Managing User Privileges and Roles : Oracle documentation for further reference
Oracle views : Complete guide on How to create oracle view, drop oracle views, alter oracle views, How to determine the query of the already created view
create table in oracle : Tables are the basic unit of data storage in an Oracle Database. we cover how to use Oracle create table command to create a table with a foreign key /primary key
Sequences in oracle : What is Sequence in oracle, Description of all options, How to create them ,Oracle 12c new feature of sequences, How to drop and alter them
create user in oracle 12c : Find out how to create a common user in oracle 12c, create a common and local users, create common role and local role and how to manage them

Hope you like the post.Please do provide the feedback

Recommended Courses

Here is the nice Udemy Course for Oracle SQL
Oracle-Sql-Step-by-step : This course covers basic sql, joins, Creating Tables and modifying its structure, Create View, Union, Union -all and much other stuff. A great course and must-have course for SQL starter
The Complete Oracle SQL Certification Course : This is a good course for anybody who wants to be Job ready for SQL developer skills. A nice explained course
Oracle SQL Developer: Essentials, Tips and Tricks : Oracle Sql developer tool is being used by many developers. This course gives us tricks and lessons on how to effectively use it and become a productive sql developer
Oracle SQL Performance Tuning Masterclass 2020 : Performance tuning is one of the critical and most sought skills. This is a good course to learn about it and start doing sql performance tuning