& TWO TYPES of USERS IN ORACLE
q COMMON USER [THIS SORT OF USER IS AVAILABLE
FOR CONTAINER & PLUGGABLE DATABASE]
q LOCAL USER [THIS SORT OF USER IS AVAILABLE FOR PLUGGABLE DATABASE]
& WHAT IS THE DIFFERENCE BETWEEN USER AND SCHEMA?
v USER - IF YOU CREATE A USER, DOESN'T HAVE OBJECT WHICH IS CALLED USER
v SCHEMA - IF USER HAS CREATED OBJECT THAT IS CALLED SCHEMA [SCHEMA
CONTAIN THE OBJECT]
& USERS DATA DICTIONARY VIEW
q DBA_USERS
Ü COMMON USER [C##TESTUSER]
q [oracle@practiceserver ~]$ sqlplus / as sysdba
SQL*Plus: Release
19.0.0.0.0 - Production on Mon Aug 28 19:24:52 2023
Version 19.3.0.0.0
Copyright (c) 1982,
2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c
Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
q SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
q SQL> create user testuser identified by
testpassword;
create user testuser
identified by testpassword
*
ERROR at line 1:
ORA-65096: invalid
common user or role name
& [COMMON USERNAME WILL always START with C##]
q SQL> create user c##testuser identified by
testpassword;
User created.
q SQL> select username from dba_users where username='C##TESTUSER';
USERNAME
--------------------------------------------------------------------------------
C##TESTUSER
q SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------ ------------------- -------------------
2 PDB$SEED READ ONLY NO
3 PDBORCL
READ WRITE NO
4 PDBMAX
READ WRITE NO
q SQL> alter session set container=pdborcl;
Session altered.
q SQL> show con_name
CON_NAME
--------------------
PDBORCL
q SQL> select username from dba_users where
username='C##TESTUSER';
USERNAME
------------------------
C##TESTUSER
& LOCAL USER [PDBUSER]
q [oracle@practiceserver ~]$ sqlplus / as sysdba
SQL*Plus: Release
19.0.0.0.0 - Production on Mon Aug 28 19:24:52 2023
Version 19.3.0.0.0
Copyright (c) 1982,
2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c
Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
q SQL> alter session set container=pdborcl;
Session altered.
q SQL> show con_name
CON_NAME
------------------------------
PDBORCL
q SQL> create user pdbuser identified by
pdbpassword;
User created.
q SQL> select username from dba_users where
username='PDBUSER';
USERNAME
--------------------------------------------------------------------------------
PDBUSER
& LOCAL USER WILL NOT BE AVAILABLE in CONTAINER
q SQL> alter session set container=CDB$ROOT;
Session altered.
q SQL> select username from dba_users where
username='PDBUSER';
no rows selected
 |
q [oracle@practiceserver ~]$ export
ORACLE_PDB_SID=pdborcl
q [oracle@practiceserver ~]$ sqlplus / as sysdba
SQL*Plus: Release
19.0.0.0.0 - Production on Mon Aug 28 20:34:29 2023
Version 19.3.0.0.0
Copyright (c) 1982,
2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c
Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
q SQL> show con_name
CON_NAME
------------------------------
PDBORCL
q SQL> grant create session to pdbuser;
Grant succeeded.
Ü CHANGE PDBUSER PASSWORD
q SQL> show user
USER is
"SYS"
q SQL> alter user pdbuser identified by testpassword;
User altered.
Ü PDBUSER LOCK & UNLOCK
q SQL> show user
USER is
"SYS"
q SQL> select username, account_status from
dba_users where username='PDBUSER';
USERNAME
ACCOUNT_STATUS
------------------------------
--------------------------------
PDBUSER
OPEN
q SQL> alter user pdbuser account lock;
User altered.
q SQL> select username, account_status from
dba_users where username='PDBUSER';
USERNAME
ACCOUNT_STATUS
------------------------------
--------------------------------
PDBUSER
LOCKED
q SQL> alter user pdbuser account unlock;
User altered.
q SQL> select username, account_status from
dba_users where username='PDBUSER';
USERNAME
ACCOUNT_STATUS
------------------------------
--------------------------------
PDBUSER
OPEN
Ü PDBUSER PASSWORD EXPIRE & Set new password
q SQL> show user
USER is
"SYS"
q SQL> alter user pdbuser password expire;
User altered.
q SQL> select username, account_status from
dba_users where username='PDBUSER';
USERNAME
ACCOUNT_STATUS
------------------------------
--------------------------------
PDBUSER
EXPIRED
q [oracle@practiceserver ~]$ sqlplus
pdbuser/testpassword@pdborcl
SQL*Plus: Release
19.0.0.0.0 - Production on Mon Aug 28 21:19:45 2023
Version 19.3.0.0.0
Copyright (c) 1982,
2019, Oracle. All rights reserved.
ERROR:
ORA-28001: the
password has expired
Changing password for
pdbuser
New password:
Retype new password:
Password changed
Connected to:
Oracle Database 19c
Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
q SQL> select username, account_status from
dba_users where username='PDBUSER';
USERNAME
ACCOUNT_STATUS
------------------------------
--------------------------------
PDBUSER
OPEN
Ü CHECK & CHANGE Default Tablespace
q SQL> show user
USER is
"SYS"
q SQL> select username, default_tablespace
from dba_users where username='PDBUSER';
USERNAME
DEFAULT_TABLESPACE
------------------------------
------------------------------
PDBUSER
USERS
q SQL> alter user pdbuser default tablespace users;
User altered.
q SQL> select username, default_tablespace
from dba_users
where username='PDBUSER';
USERNAME
DEFAULT_TABLESPACE
------------------------------
-----------------------------------
PDBUSER
USERS
Ü CHECK & CHANGE Temporary Tablespace
q SQL> show user
USER is
"SYS"
q SQL> select username, temporary_tablespace
from dba_users where username='PDBUSER';
USERNAME
TEMPORARY_TABLESPACE
------------------------------
------------------------------
PDBUSER
TEMP
q SQL> alter user pdbuser temporary
tablespace temp01;
User altered.
q SQL> select username, temporary_tablespace
from dba_users where username='PDBUSER';
USERNAME
TEMPORARY_TABLESPACE
------------------------------
------------------------------
PDBUSER
TEMP01
Ü DELETE user
q SQL> drop user pdbuser;
User dropped.
Ü DELETE user with Object
q SQL> drop user pdbuser cascade;
User dropped.
Ü QUOTA
[A quota is set for how much memory space a user can use in a
tablespace.]
[oracle@dbserver ~]$
sqlplus / as sysdba
SQL*Plus: Release
19.0.0.0.0 - Production on Wed Jan 24 23:11:07 2024
Version 19.3.0.0.0
Copyright (c) 1982,
2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c
Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> alter session
set container=maxpdb;
Session altered.
q SQL> create user pdbuser identified by
pdbuser default tablespace users_data quota 500m on users_data;
q SQL> alter user pdbuser quota 500m on
users_data;
q USER_TS_QUOTAS
[oracle@dbserver ~]$
sqlplus / as sysdba
SQL*Plus: Release
19.0.0.0.0 - Production on Wed Jan 24 23:11:07 2024
Version 19.3.0.0.0
Copyright (c) 1982,
2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c
Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> conn pdbuser/pdbuser@maxpdb;
Connected.
SQL> select * from USER_TS_QUOTAS;
TABLESPACE_NAME BYTES
MAX_BYTES BLOCKS MAX_BLOCKS DRO
------------------------------
---------- ------------------ ------------ --------------------- ------
USERS 0 524288000 0
64000 NO
Ü PRIVILEGES
[A user privilege is a right to execute a particular type of SQL statement, or a right to access database object.]
q Two types of privilege:
v SYSTEM PRIVILEGE
o Rights to Create, Modify and Delete object
o WITH ADMIN OPTION
v OBJECT PRIVILEGE
o Ability to perform specific operations on
objects
o WITH GRANT OPTION
Ü SYSTEM PRIVILEGE
q DBA_SYS_PRIVS [SYSTEM]
GRANTEE - যাকে privilege দেওয়া হয়েছে (User-SYS, Role-DBA বা PUBLIC)
PRIVILEGE - কোন system privilege দেওয়া হয়েছে
ADMIN_OPTION - YES হলে—গ্রাহক (grantee) অন্য কাউকে এই privilege আবার দিতে পারবে, NO হলে—শুধু সে নিজে ব্যবহার করতে পারবে, অন্য কাউকে দিতে পারবে না
COMMON - YES: এই privilege common user-কে দেওয়া, তাই সব PDB-তেই প্রযোজ্য, NO: শুধু নির্দিষ্ট PDB-তে প্রযোজ্য
INHERITED - YES: privilege সরাসরি দেওয়া হয়নি, বরং কোনো role থেকে পাওয়া, NO: privilege ইউজারকে সরাসরি grant করা হয়েছে
Ü GRANT system privilege to a user
[oracle@dbserver
~]$ sqlplus / as sysdba
SQL*Plus:
Release 19.0.0.0.0 - Production on Thu Jan 25 12:21:17 2024
Version
19.3.0.0.0
Copyright
(c) 1982, 2019, Oracle. All rights
reserved.
Connected
to:
Oracle
Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version
19.3.0.0.0
SQL>
show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL>
alter session set container=maxpdb;
Session
altered.
q SQL> grant create session, create any
table, alter any table to pdbuser;
Grant succeeded.
q SQL> select privilege, grantee from
dba_sys_privs
where grantee='PDBUSER';
PRIVILEGE
GRANTEE
-----------------------------
-------------------------
CREATE ANY TABLE
PDBUSER
ALTER ANY TABLE
PDBUSER
CREATE SESSION
PDBUSER
q ADMIN option – Who has right to give permission to another user.
q SQL> grant create any table to test with
admin option;
Grant succeeded.
Ü REVOKE system privilege from a user
q SQL> revoke create any table from pdbuser;
Revoke succeeded.
q SQL> select privilege, grantee from
dba_sys_privs where grantee='PDBUSER';
PRIVILEGE
GRANTEE
-------------------------
-------------------------
ALTER ANY TABLE
PDBUSER
CREATE SESSION
PDBUSER
Ü OBJECT PRIVILEGE
q DBA_TAB_PRIVS [OBJECT]
GRANTEE - যে ইউজার বা রোলকে privilege দেওয়া হয়েছে
OWNER - টেবিলটি যে ইউজারের অধীনে আছে
TABLE_NAME - কোন object-এ অনুমতি দেওয়া হয়েছে
GRANTOR - অনুমতি কে দিয়েছে
PRIVILEGE - কোন privilege দেওয়া হয়েছে
GRANTABLE - অন্য ইউজারকে পুনরায় দেওয়ার ক্ষমতা আছে কি না
HIERARCHY - সাধারণত VIEW privilege-এ ব্যবহৃত
q SQL> select distinct privilege from DBA_TAB_PRIVS;
Ü GRANT Object privilege
q SQL> grant insert, update, delete on
emptable to pdbuser;
Grant succeeded.
q SQL> grant select on hr.emptable to pdbuser;
Grant succeeded.
Ü REVOKE object privilege
q SQL> revoke update on emptable from pdbuser;
Revoke succeeded.
q SQL> revoke all on emptable from pdbuser;
Revoke succeeded.
Ü ROLE
r COLLECTION OF PRIVILEGES
& TWO TYPES OF ROLES
q SYSTEM LEVEL
q OBJECT LEVEL
q ROLE_SYS_PRIVS [SYSTEM LEVEL]
[oracle@dbserver ~]$ sqlplus / as sysdba
SQL*Plus:
Release 19.0.0.0.0 - Production on Thu Jan 25 12:21:17 2024
Version
19.3.0.0.0
Copyright
(c) 1982, 2019, Oracle. All rights
reserved.
Connected
to:
Oracle
Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version
19.3.0.0.0
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> alter session set container=maxpdb;
Session altered.
q CREATE A Role
q SQL> create role DEV_ROLE;
Role created.
Ü GRANT SYSTEM privileges to ROLE
q SQL> grant create session, create any table
to dev_role;
q SQL> select role, privilege from
role_sys_privs where role='DEV_ROLE';
ROLE
PRIVILEGE
---------------- ----------------------------------------
DEV_ROLE
CREATE SESSION
DEV_ROLE
CREATE ANY TABLE
Ü REVOKE system privilege from a ROLE
q SQL> revoke create any table from dev_role;
Revoke succeeded.
q ROLE_TAB_PRIVS
[OBJECT LEVEL]
Ü GRANT OBJECT privileges to ROLE
q SQL> grant select, insert, update, delete
on hr.emp to dev_role;
q SQL> select role, owner, table_name, privilege
from role_tab_privs
where role='DEV_ROLE';
ROLE
OWNER TABLE_NAME
PRIVILEGE
---------------
------------ -------------------- --------------------------------------------------
DEV_ROLE
HR
EMP SELECT, INSERT, UPDATE, DELTE
Ü REVOKE OBJECT privilege from a ROLE
q SQL> revoke insert, update on hr.emp from dev_role;
Revoke succeeded.
Ü GRANT Role to a User
q SQL> grant dev_role to pdbuser;
q SQL> select grantee, granted_role from dba_role_privs where granted_role='DEV_ROLE';
GRANTEE
GRANTED_ROLE
--------------- -----------------------
SYS
DEV_ROLE
PDBUSER DEV_ROLE
Ü DROP A Role
q SQL> drop role DEV_ROLE;
Role dropped.
r DBA_ROLE_PRIVS [In
which role is assigned for user]
o
select granted_role from dba_role_privs where grantee=’HR’;
Ü DEFAULT role Set/Unset
q SQL> alter user pdbuser default role
DEV_ROLE;
User altered.
q SQL> alter user pdbuser default role NONE;
User altered.
Ü PROFILE
& PROFILES
r LIMIT OF THE DATABASE RESOURCES
q UNLIMITED [BY DEFAULT]
q USER DEFINED
v RESOURCE LEVEL
v SESSION_PER_USER
v IDLE_TIME
v CONNECT_TIME
v PASSWORD LEVEL
v FAILED_LOGIN_ATTEMPTS
v PASSWORD_LOCK_TIME
& DBA_PROFILES
Ü DEFAULT PROFILE SETTING:
v SESSION_PER_USER – No. of allowed concurrent
sessions for a user
v CPU_PER_SESSION – CPU time limit for a
session, expressed in hundredth of seconds.
v CPU_PER_CALL – Specify the CPU time limit for
a call (a parse, execute, or fetch), expressed in hundredths of seconds.
v CONNECT_TIME – Specify the total elapsed time
limit for a session, expressed in minutes.
v IDLE_TIME – Specify the permitted periods of
continuous inactive time during a session, expressed in minutes.
v 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).
v PRIVATE_SGA – SGA a session can allocate in
the shared pool of the system global area (SGA), expressed in bytes.
v FAILED_LOGIN_ATTEMPTS – No. of failed attempts
to log in to the user account before the account is locked
v PASSWORD_LIFE_TIME: No. of days the account
will be open. after that, it will expire.
v PASSWORD_REUSE_TIME: number of days before
which a password cannot be reused
v PASSWORD_REUSE_MAX: number of days before
which a password can be reused
v PASSWORD_LOCK_TIME: Number of days the user
account remains locked after a failed login
v PASSWORD_GRACE_TIME: Number of grace days for
the user to change the password
v PASSWORD_VERIFY_FUNCTION: PL/SQL that can be used for password verification
[oracle@dbserver ~]$ sqlplus / as sysdba
SQL*Plus:
Release 19.0.0.0.0 - Production on Thu Jan 25 12:21:17 2024
Version
19.3.0.0.0
Copyright
(c) 1982, 2019, Oracle. All rights
reserved.
Connected
to:
Oracle
Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version
19.3.0.0.0
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> alter session set container=maxpdb;
Session
altered.
q SQL> show user
USER is
"SYS"
q SQL> select PROFILE, RESOURCE_NAME,
RESOURCE_TYPE, LIMIT from dba_profiles where profile='DEFAULT';
q SQL> select username, profile from
dba_users where username='PDBUSER';
USERNAME
PROFILE
------------------------------
--------------------
PDBUSER DEFAULT
Ü 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;
Ü ALTER A PROFILE
q SQL> alter profile APP_PROFILE limit
PASSWORD_LIFE_TIME 90;
Profile altered.
q SQL> select RESOURCE_NAME, RESOURCE_TYPE,
LIMIT from dba_profiles where profile='DEFAULT' and RESOURCE_NAME='PASSWORD_LIFE_TIME';
RESOURCE_NAME
RESOURCE LIMIT
------------------------------
-------- -----------------
PASSWORD_LIFE_TIME
PASSWORD 90
Ü CHANGE PROFILE of a user
q SQL> alter user pdbuser profile app_profile;
User altered.
q SQL> select username, profile from
dba_users where username='PDBUSER';
USERNAME
PROFILE
------------------------------
--------------------
PDBUSER APP_PROFILE
Ü DELETE PROFILE of a user
q SQL> drop profile app_profile cascade;
Profile dropped.
q SQL> select * from ALL_TABLES where owner='HR';