📋Steps in this guide1/10

Oracle User Management

DEFAULT PROFILE SETTING:

oracle configurationintermediate

by OracleDba

12 views

Overview

Code/Command (click line numbers to comment):

SQL>show con_name;
CON_NAME
----------------------
CDB$ROOT
SQL>;CREATE USER c##CDBUSER IDENTIFIED BY oracle
User created.

create user TESTUSER identified by oracle PROFILE DEFAULT DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;

create user PDBUSER identified by oracle1234 PROFILE DEFAULT DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;

grant unlimited tablespace to PDBUSER;

alter user PDBUSER identified by oracle;

alter user PDBUSER account lock;

alter user PDBUSER account unlock;

Section 2

DEFAULT PROFILE SETTING: *SESSION_PER_USER – No. of allowed concurrent sessions for a user

Code/Command (click line numbers to comment):

alter user PDBUSER password expire;

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

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

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

Section 3

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

Section 4

*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

Section 5

*PASSWORD_VERIFY_FUNCTION: PL/SQL that can be used for password verification 11. How to make a user non-expiry ? Usually application users we need to set non-expiry. I.e it will never expire.

Code/Command (click line numbers to comment):

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;

ALTER PROFILE APP_PROFILE LIMIT FAILED_LOGIN_ATTEMPS UNLIMITED;

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

Section 6

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

Code/Command (click line numbers to comment):

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

Section 7

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. OBJECT PRIVILEGE:

Code/Command (click line numbers to comment):

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

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

REVOKE create any table from PDBUSER;

Section 8

An object privilege is the right to perform a particular action on an object or to access another user’s object. ROLE: A role is a collection of privileges. It allows easier management of privileges.

Code/Command (click line numbers to comment):

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

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

revoke update on siebel.test2 from PDBUSER;

Section 9

18.Create a role:

Code/Command (click line numbers to comment):

create role DEV_ROLE;

create role DEV_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

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

drop user PDBUSER cascade;

Section 10

23. Dynamic Query for user management

Code/Command (click line numbers to comment):

Drop role DEV_ROLE;

select 'alter user ' || username ||' identified by values ''' || password || ''';'from dba_users;

select 'alter user ' || username ||' identified by values ''' || password || ''';'from dba_users;

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;

Comments (0)

Please to add comments

No comments yet. Be the first to comment!