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