Oracle 12C User Management

In Oracle 12C , there are two types of user.

  1. Common user
  2. Local User

COMMON USER:

1.A common user is a DB user, which work perform an activity in all the containers including root container of the CDB.
2.A common user can only be created in root container.
3.Common username must start with C##.
4.While creating a common user, we can’t mention container=CURRENT. Either mention container=ALL or don’t use container keyword.
5.It is not recommended to create objects under common user

Syntax to Create Common User

SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> create user C##TESTUSER identified by TESTUSERPASSWORD; 
User created.

LOCAL USER

LOCAL USERS are a database user that exists only in a single PDB.  LOCAL USERS can have administrative privileges, but these privileges are localized to the PDB what the account is created in.  LOCAL USERS have the following characteristics:

  1. LOCAL USER accounts cannot create user accounts or commonly grant them privileges.
  2. Can grant local user accounts common roles. However, the privileges associated with the common role only apply to the local user’s PDB
  3. LOCAL USER account must be unique only within its PDB
  4. With the appropriate privileges, a local user can access object in a common user’s schema
  5. Can be editions-enable a local user account but not a common user account
create user TESTUSER identified by DEV_CLASS#1234 PROFILE DEFAULT DEFAULT TABLESPACE USERSTEMPORARY TABLESPACE TEMP;

Local User Creation

create user TESTUSER identified by DEV_CLASS#1234 PROFILE DEFAULT DEFAULT TABLESPACE USERSTEMPORARY TABLESPACE TEMP;

Minimum privilege required to connect to a database is create session

grant create session to TESTUSER;

2. Change password of a user:

alter user DEV_CLASS identified by DEV_CLASS#91234;

3. Lock/unlock a user

alter user dev_class account lock; 
alter user dev_class account unlock;

Make a user password expiry:

When we make a user id expiry, then when the user does login, it will prompt him to set a new password.

alter user dev_class account expire;

5. Changing default tablespace of a user:

select username,default_tablespace from dba_users where username='DEV_CLASS'; 

USERNAME                DEFAULT_TABLESPACE
----------------------- ------------------------------
DEV_CLASS                   USERS 

alter user  DEV_CLASS default tablespace DATATS; 

select username,default_tablespace from dba_users where username='DEV_CLASS'; 

USERNAME                DEFAULT_TABLESPACE
----------------------- ------------------------------
TESTUSER                   DATATS

6. Changing default TEMP tablespace of a user

SQL> select username,TEMPORARY_TABLESPACE from dba_users where username='TESTUSER';

USERNAME TEMPORARY_TABLESPACE
-------  ------------------------------
TESTUSER  TEMP

alter user TESTUSER temporary tablespace TEMP2;

SQL> select username,TEMPORARY_TABLESPACE from dba_users where username='TESTUSER';

USERNAME TEMPORARY_TABLESPACE
--------- ----------------------
DEV_CLASS TEMP2

PROFILE:




A profile enforces set of password security rules and resource usage limit.
While creating a user if no profile is mentioned, then DEFAULT profile will be assigned.

col
limit for a12

col profile for a14

set lines 200

set pagesize 200

select profile,resource_name,RESOURCE_TYPE,limit from dba_profiles where profile='DEFAULT';




PROFILE        RESOURCE_NAME                    RESOURCE LIMIT

-------------- -------------------------------- -------- ------------

DEFAULT        COMPOSITE_LIMIT                  KERNEL   UNLIMITED

DEFAULT        SESSIONS_PER_USER                KERNEL   UNLIMITED

DEFAULT        CPU_PER_SESSION                  KERNEL   UNLIMITED

DEFAULT        CPU_PER_CALL                     KERNEL   UNLIMITED

DEFAULT        LOGICAL_READS_PER_SESSION        KERNEL   UNLIMITED

DEFAULT        LOGICAL_READS_PER_CALL           KERNEL   UNLIMITED

DEFAULT        IDLE_TIME                        KERNEL   UNLIMITED

DEFAULT        CONNECT_TIME                     KERNEL   UNLIMITED

DEFAULT        PRIVATE_SGA                      KERNEL   UNLIMITED

DEFAULT        FAILED_LOGIN_ATTEMPTS            PASSWORD 10

DEFAULT        PASSWORD_LIFE_TIME               PASSWORD 180

DEFAULT        PASSWORD_REUSE_TIME              PASSWORD UNLIMITED

DEFAULT        PASSWORD_REUSE_MAX               PASSWORD UNLIMITED

DEFAULT        PASSWORD_VERIFY_FUNCTION         PASSWORD NULL

DEFAULT        PASSWORD_LOCK_TIME               PASSWORD 1

DEFAULT        PASSWORD_GRACE_TIME              PASSWORD 7

DEFAULT PROFILE SETTING:

*SESSION_PER_USER – No. of allowed concurrent sessions for a user
*CPU_PER_SESSION – CPU time limit for a session, expressed in hundredth of seconds.
*CPU_PER_CALL – Specify the CPU time limit for a call (a parse, execute, or fetch), expressed in hundredths of seconds.
*CONNECT_TIME – Specify the total elapsed time limit for a session, expressed in minutes.
*IDLE_TIME – Specify the permitted periods of continuous inactive time during a session, expressed in minutes.
*LOGICAL_READS_PER_SESSION – Specify the permitted number of data blocks read in a session, including blocks read from memory and disk
*LOGICAL_READS_PER_CALL –permitted number of data blocks read for a call to process a SQL statement (a parse, execute, or fetch).
*PRIVATE_SGA – SGA a session can allocate in the shared pool of the system global area (SGA), expressed in bytes.
*FAILED_LOGIN_ATTEMPTS – No. of failed attempts to log in to the user account before the account is locked
*PASSWORD_LIFE_TIME: No. of days the account will be open. after that it will expiry.
*PASSWORD_REUSE_TIME: number of days before which a password cannot be reused
*PASSWORD_REUSE_MAX: number of days before which a password can be reused
*PASSWORD_LOCK_TIME: Number of days the user account remains locked after failed login
*PASSWORD_GRACE_TIME: Number of grace days for user to change password
*PASSWORD_VERIFY_FUNCTION: PL/SQL that can be used for password verification

8. Create a new profile

CREATE PROFILE “APP_PROFILE”
LIMIT
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME 90
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME 180
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_GRACE_TIME UNLIMITED;

9. Alter a profile

ALTER PROFILE APP_PROFILE LIMIT FAILED_LOGIN_ATTEMPS UNLIMITED;

10. Change profile of an user:

SQL> select username,profile from dba_users where username='TESTUSER';
USERNAME PROFILE
-------  ---------
DEV_CLASS DEFAULT

ALTER USER SCOTT PROFILE APP_PROFILE;

SQL> select username,profile from dba_users where username='TESTUSER';
USERNAME PROFILE
-------  ----------
TESTUSER APP_PROFILE


10. Change profile of an user:

SQL> select username,profile from dba_users where username='DEV_CLASS';
USERNAME PROFILE
----------------------- ------------------------------
DEV_CLASS DEFAULT
 
ALTER USER SCOTT PROFILE APP_PROFILE;
 
SQL> select username,profile from dba_users where username='TESTUSER';
 
USERNAME PROFILE
----------------------- ------------------------------
TESTUSER APP_PROFILE

11. How to make a user non-expiry

Usually application users we need to set non-expiry. I.e it will never expire. To set it, we need to either create a profile with PASSWORD_LIFE_TIME UNLIMITED or alter the profile of that user.

SQL> select username,profile,EXPIRY_DATE from dba_users where username='TESTUSER';

USERNAME PROFILE EXPIRY_DATE
-------- ------- -----------
DEV_CLASS APP_PROFILE 16-AUG-17
ALTER PROFILE APP_PROFILE LIMIT PASSWORD_LIFE_TIME UNLIMITED;
SQL> select username,profile,EXPIRY_DATE from dba_users where username='TESTUSER';
USERNAME PROFILE EXPIRY_DATE

TESTUSER APP_PROFILE

PRIVILEGES:

A privilege is a permission to execute either a particular type of sql statements or to perform particular action on database objects.

Two type of privilege:
1. SYSTEM PRIVILEGE
2. OBJECT PRIVILEGE

SYSTEM PRIVILEGE

A system privilege is the right to perform a particular action or to perform an action on any object of a particular type.

12.List of all system privileges:

SQL>select distinct privilege from dba_sys_privs;

PRIVILEGE

SQL>select distinct privilege from dba_sys_privs;

CREATE SESSION
CREATE OPERATOR
CREATE VIEW
CREATE ANY PROCEDURE
CREATE DATABASE LINK
DEQUEUE ANY QUEUE
DEBUG ANY PROCEDURE
CREATE PUBLIC SYNONYM
SELECT ANY TRANSACTION
READ ANY TABLE
CREATE ASSEMBLY
EXECUTE ANY INDEXTYPE
CREATE ANY TYPE
ANALYZE ANY
DROP PUBLIC SYNONYM
AUDIT SYSTEM
EXECUTE ANY ASSEMBLY
CREATE ANY EDITION
ADMINISTER ANY SQL TUNING SET
DROP ANY RULE SET
CREATE ANY EVALUATION CONTEXT
ADMINISTER DATABASE TRIGGER
ADMINISTER RESOURCE MANAGER
GRANT ANY PRIVILEGE
ALTER RESOURCE COST
ALTER ANY TRIGGER
DROP ANY SYNONYM
CREATE USER
CREATE SQL TRANSLATION PROFILE
EM EXPRESS CONNECT
CREATE ANY TRIGGER
EXEMPT REDACTION POLICY
CREATE DIMENSION
CREATE RULE SET
EXECUTE ANY EVALUATION CONTEXT
ALTER ANY OUTLINE
UNDER ANY TYPE
ALTER ANY ROLE
CREATE ANY MINING MODEL
DROP ANY OUTLINE
ALTER ANY INDEX
UPDATE ANY TABLE
CREATE TABLESPACE
USE ANY SQL TRANSLATION PROFILE
DROP ANY VIEW
CREATE ANY SQL TRANSLATION PROFILE
BECOME USER
DROP ANY MEASURE FOLDER
CREATE ANY CUBE
CREATE ANY OUTLINE
COMMENT ANY MINING MODEL
ALTER ANY INDEXTYPE
DROP PROFILE
CREATE PROCEDURE
CREATE SEQUENCE
CREATE JOB
EXEMPT ACCESS POLICY
QUERY REWRITE
EXECUTE ANY RULE SET
CREATE PLUGGABLE DATABASE
ALTER ANY CUBE
ALTER ANY RULE SET
UNDER ANY VIEW
DROP ANY PROCEDURE
CREATE ROLE
CREATE ANY TABLE
RESTRICTED SESSION
ALTER ANY MEASURE FOLDER
ADVISOR
IMPORT FULL DATABASE
DROP ANY TRIGGER
ALTER ANY PROCEDURE
SELECT ANY SEQUENCE
CREATE ANY CONTEXT
UNDER ANY TABLE
ALTER PROFILE
FORCE TRANSACTION
DROP ANY MINING MODEL
CREATE ANY OPERATOR
CREATE PUBLIC DATABASE LINK
MANAGE ANY FILE GROUP
MANAGE TABLESPACE
CREATE CUBE DIMENSION
UNLIMITED TABLESPACE
SELECT ANY TABLE
CREATE EVALUATION CONTEXT
ON COMMIT REFRESH
CREATE ANY INDEX
EXECUTE ANY PROGRAM
ALTER ANY CUBE BUILD PROCESS
CREATE ANY MEASURE FOLDER
EXECUTE ASSEMBLY
CREATE ANY SQL PROFILE
ALTER ANY TYPE
CREATE PROFILE
EXECUTE ANY PROCEDURE
CREATE ANY CLUSTER
CREATE ANY ASSEMBLY
CREATE ANY RULE
EXECUTE ANY TYPE
ALTER ANY CLUSTER
DROP ANY CUBE
DROP PUBLIC DATABASE LINK
SELECT ANY MEASURE FOLDER
REDEFINE ANY TABLE
SELECT ANY CUBE
CREATE ANY INDEXTYPE
CREATE ANY CUBE DIMENSION
EXEMPT DDL REDACTION POLICY
MANAGE SCHEDULER
ALTER SESSION
CREATE TRIGGER
CREATE MATERIALIZED VIEW
ALTER ANY SEQUENCE
EXEMPT IDENTITY POLICY
CREATE ANY CREDENTIAL
SET CONTAINER
GLOBAL QUERY REWRITE
ALTER ANY LIBRARY
GRANT ANY ROLE
ALTER USER
CREATE MEASURE FOLDER
UPDATE ANY CUBE
READ ANY FILE GROUP
GRANT ANY OBJECT PRIVILEGE
DROP ANY OPERATOR
CREATE CREDENTIAL
CHANGE NOTIFICATION
CREATE ANY SYNONYM
INSERT ANY TABLE
EXEMPT DML REDACTION POLICY
EXECUTE ANY RULE
INSERT ANY MEASURE FOLDER
DROP ANY CUBE DIMENSION
ALTER ANY ASSEMBLY
LOGMINING
CREATE ANY VIEW
CREATE TYPE
FLASHBACK ARCHIVE ADMINISTER
ADMINISTER SQL MANAGEMENT OBJECT
ALTER ANY MINING MODEL
SELECT ANY MINING MODEL
CREATE EXTERNAL JOB
DROP ANY EVALUATION CONTEXT
CREATE LIBRARY
DROP ANY SQL TRANSLATION PROFILE
CREATE MINING MODEL
DROP ANY CONTEXT
MANAGE ANY QUEUE
DROP ANY DIMENSION
CREATE ANY DIMENSION
CREATE ANY LIBRARY
DROP ANY MATERIALIZED VIEW
CREATE ANY MATERIALIZED VIEW
ALTER DATABASE
DROP ANY ROLE
LOCK ANY TABLE
DROP USER
DROP TABLESPACE
MERGE ANY VIEW
DROP ANY TYPE
COMMENT ANY TABLE
ALTER TABLESPACE
CREATE CUBE
ALTER ANY SQL PROFILE
DROP ANY INDEXTYPE
ALTER ROLLBACK SEGMENT
DROP ANY CUBE BUILD PROCESS
CREATE ANY CUBE BUILD PROCESS
DELETE ANY CUBE DIMENSION
ANALYZE ANY DICTIONARY
CREATE TABLE
ALTER ANY TABLE
SELECT ANY DICTIONARY
CREATE CLUSTER
DEBUG CONNECT SESSION
CREATE INDEXTYPE
INHERIT ANY PRIVILEGES
DROP ANY SQL PROFILE
CREATE ANY DIRECTORY
DROP ANY INDEX
ENQUEUE ANY QUEUE
DROP ANY CLUSTER
SELECT ANY CUBE BUILD PROCESS
ADMINISTER KEY MANAGEMENT
ALTER ANY SQL TRANSLATION PROFILE
DROP ANY EDITION
CREATE ROLLBACK SEGMENT
SELECT ANY CUBE DIMENSION
ALTER ANY EVALUATION CONTEXT
FORCE ANY TRANSACTION
INSERT ANY CUBE DIMENSION
ALTER ANY OPERATOR
EXECUTE ANY LIBRARY
ALTER ANY MATERIALIZED VIEW
ALTER ANY CUBE DIMENSION
CREATE SYNONYM
FLASHBACK ANY TABLE
CREATE RULE
EXECUTE ANY CLASS
CREATE ANY SEQUENCE
ALTER SYSTEM
UPDATE ANY CUBE DIMENSION
UPDATE ANY CUBE BUILD PROCESS
CREATE CUBE BUILD PROCESS
DROP ANY ASSEMBLY
ADMINISTER SQL TUNING SET
EXECUTE ANY OPERATOR
DROP ANY LIBRARY
AUDIT ANY
DELETE ANY TABLE
RESUMABLE
DROP ANY TABLE
ALTER ANY EDITION
EXPORT FULL DATABASE
DROP ANY DIRECTORY
DROP ANY SEQUENCE
DROP ROLLBACK SEGMENT
CREATE ANY JOB
BACKUP ANY TABLE
DELETE ANY MEASURE FOLDER
MANAGE FILE GROUP
DROP ANY RULE
ALTER ANY DIMENSION
CREATE ANY RULE SET
ALTER ANY RULE

13.Grant a system privilege to a user:

Grant create any table,alter any table to TESTUSER;
 
SQL>  select privilege,grantee from dba_sys_privs where grantee='TESTUSER';
 
PRIVILEGE                                GRANTEE
---------------------------------------- ---------
CREATE ANY TABLE                         DEV_CLASS
ALTER ANY TABLE                          DEV_CLASS

14. Revoke a system privilege from a user:

REVOKE create any table from dev_class;

OBJECT PRIVILEGE:

An object privilege is the right to perform a particular action on an object or to access another user’s object.

15.list of object privileges:


SQL> select distinct privilege from DBA_TAB_PRIVS;
 
PRIVILEGE
----------------------------------------
EXECUTE
SELECT
INSERT
INDEX
DEQUEUE
USE
QUERY REWRITE
READ
ON COMMIT REFRESH
REFERENCES
INHERIT PRIVILEGES
DEBUG
ALTER
UPDATE
WRITE
FLASHBACK
DELETE

16.Grant object privilege:

grant insert,update,delete on SIEBEL.TEST2 to TESTUSER;
 
-- grant execute on a procedure
 
grant execute on SIEBLE.DAILYPROC to TESTUSER;
 
-- View the granted object privilege:
 
select grantee,owner,table_name,privilege from dba_tab_privs where grantee='TESTUSER';

17.Revoke object privilege:

revoke update on siebel.test2 from TESTUSER;

ROLE:

A role is a collection of privileges. It allows easier management of privileges.

17.Create a role:

create role DEV_ROLE;

18.Grant privileges to a role:

grant create session to dev_role;
grant select any table to dev_role;
grant insert on siebel.test2 to dev_role;
 
-- List of  SYSTEM privileges granted to a ROLE
 
SQL>  select role,privilege from role_sys_privs where role='DEV_ROLE';
 
ROLE         PRIVILEGE
------------ ----------------------------------------
DEV_ROLE     CREATE SESSION
DEV_ROLE     SELECT ANY TABLE
 
-- List of OBJECT privileges granted to ROLE;
 
SQL> select role,owner,table_name,privilege from role_tab_privs where  role='DEV_ROLE';
 
ROLE         OWNER        TABLE_NAME   PRIVILEGE
------------ ------------ ------------ ----------------------------------------
DEV_ROLE     SIEBEL       TEST2          INSERT

19. Grant role to a User:

grant dev_role to dev_class;
 
-- List of the user and granted role:
 
SQL> select grantee,GRANTED_ROLE from dba_role_privs where granted_role='DEV_ROLE';
 
GRANTEE      GRANTED_ROLE
------------ -----------------------
SYS          DEV_ROLE
DEV_CLASS    DEV_ROLE

20. Drop a user:

Dropping a user will drop all the objects it owns.

drop user TESTUSER cascade;

21. Drop a Role:

Drop role DEV_ROLE;

Share and Enjoy !

0Shares
0 0 0

Recommended For You

ankushthavali

About the Author: ankushthavali

Leave a Reply

Your email address will not be published. Required fields are marked *