- 26 Jul, 2020
- 0 Comments
- 11 Mins Read
Oracle User Management
User Management in Oracle
In this Blog , we will see Different types of users in Oracle. Starting from Oracle 12C , there are two types of user.
Common user
Local User
COMMON USER:
A common user is a DB user, which work perform an activity in all the containers including root container of the CDB.
A common user can only be created in root container.
Common username must start with C##.
While creating a common user, we can’t mention container=CURRENT. Either mention container=ALL or don’t use container keyword.
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##CDBUSER IDENTIFIED BY oracle 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: LOCAL USER accounts cannot create user accounts or commonly grant them privileges. Can grant local user accounts common roles. However, the privileges associated with the common role only apply to the local user’s PDB LOCAL USER account must be unique only within its PDB With the appropriate privileges, a local user can access object in a common user’s schema Can be editions-enable a local user account but not a common user account
create user TESTUSER identified by oracle PROFILE DEFAULT DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;1. Local User Creation
create user PDBUSER identified by oracle1234 PROFILE DEFAULT DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;Minimum privilege required to connect to a database is create session grant create session to PDBUSER;
grant unlimited tablespace to PDBUSER;2. Change password of a user:
alter user PDBUSER identified by oracle;
3. Lock/unlock a user
alter user PDBUSER account lock; alter user PDBUSER account unlock;4. 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 PDBUSER password expire;
5. Changing default tablespace of a user:
SQL> select username,default_tablespace from dba_users where username='PDBUSER'; USERNAME DEFAULT_TABLESPACE -------------------- -------------------- PDBUSER USERS SQL> alter user PDBUSER default tablespace system; User altered. SQL> select username,default_tablespace from dba_users where username='PDBUSER'; USERNAME DEFAULT_TABLESPACE -------------------- -------------------- PDBUSER SYSTEM
6. Changing default TEMP tablespace of a user
SQL> select username,TEMPORARY_TABLESPACE from dba_users where username='PDBUSER'; USERNAME TEMPORARY_TABLESPACE -------------------- ------------------------------ PDBUSER TEMP alter user PDBUSER temporary tablespace TEMPE11; SQL> select username,TEMPORARY_TABLESPACE from dba_users where username='PDBUSER'; USERNAME TEMPORARY_TABLESPACE --------- ---------------------- PDBUSER TEMPE11
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='PDBUSER'; USERNAME PROFILE ------- --------- PDBUSER DEFAULT ALTER USER PDBUSER PROFILE APP_PROFILE; SQL> select username,profile from dba_users where username='PDBUSER'; USERNAME PROFILE ------- ---------- PDBUSER 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.
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='PDBUSER'; USERNAME PROFILE EXPIRY_DA -------------------- -------------------- --------- PDBUSER APP_PROFILE 02-OCT-24 SQL> ALTER PROFILE APP_PROFILE LIMIT PASSWORD_LIFE_TIME UNLIMITED; Profile altered. SQL> select username,profile,EXPIRY_DATE from dba_users where username='PDBUSER'; USERNAME PROFILE EXPIRY_DA -------------------- -------------------- --------- PDBUSER 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 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:
SQL> Grant create any table,alter any table to PDBUSER; Grant succeeded. SQL> select privilege,grantee from dba_sys_privs where grantee='PDBUSER'; PRIVILEGE GRANTEE ---------------------------------------- ------- CREATE ANY TABLE PDBUSER ALTER ANY TABLE PDBUSER
14. Revoke a system privilege from a user:
REVOKE create any table from PDBUSER;
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 PDBUSER; -- grant execute on a procedure grant execute on SIEBLE.DAILYPROC to PDBUSER; -- View the granted object privilege: set lines 1234 pages 1234 col grantee for a30; col owner for a30; col table_name for a30; col privilege for a30; select grantee,owner,table_name,privilege from dba_tab_privs where grantee='PDBUSER';
17.Revoke object privilege:
revoke update on siebel.test2 from PDBUSER;
ROLE:
A role is a collection of privileges. It allows easier management of privileges.
18.Create a role:
create role DEV_ROLE;
19.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
20. 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
21. Drop a user:
Dropping a user will drop all the objects it owns.
drop user PDBUSER cascade;
22. Drop a Role:
Drop role DEV_ROLE;
23.Dynamic Query for user management
select 'alter user ' || username ||' identified by values ''' || password || ''';'from dba_users;
24.DDL Query
CLEAR SCREEN;
ACCEPT uname PROMPT 'Enter User Name : ';
ACCEPT outfile PROMPT 'Output filename : ';
SPOOL &&outfile..gen;
SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON;
BEGIN
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/
SELECT dbms_metadata.get_ddl('USER', '&&uname') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', '&&uname') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', '&&uname') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', '&&uname') FROM dual;
SPOOL OFF;