An User is used to connect to database. All database objects like table,index,view etc can be created under that user.In Oracle database, users and schemas are essentially the same thing. You can consider that an user is the account you used to connect to a database, and a schema is the set of objects (tables, views, etc.) that belong to that account.
Lets, See some of user management Task done by every
Database administrator.
1. Create a user:
PROFILE DEFAULT
DEFAULT TABLESPACE
USERS
TEMPORARY TABLESPACE
TEMP;
grant connect,resource to TEST_USER;
2. Change password of a user:
Command:
alter user TEST_USER identified by TEST_USER#1431;
3.
Lock/unlock a user
alter user TEST_USER account lock;
alter user TEST_USER 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.
Command:
alter user TEST_USER account expire;
5. Changing default tablespace of a
user:
Command:
select
username,default_tablespace from dba_users where username='TEST_USER';
USERNAME
DEFAULT_TABLESPACE
-----------------------
------------------------------
TEST_USER USERS
alter user TEST_USER default tablespace DATATS;
select
username,default_tablespace from dba_users where username='TEST_USER';
USERNAME
DEFAULT_TABLESPACE
-----------------------
------------------------------
TEST_USER DATATS
6. Changing default TEMP tablespace
of a user:
SQL> select username,TEMPORARY_TABLESPACE from dba_users where username='TEST_USER';
USERNAME
TEMPORARY_TABLESPACE
-----------------------
------------------------------
TEST_USER TEMP
alter user TEST_USER temporary tablespace TEMP2;
SQL> select
username,TEMPORARY_TABLESPACE from dba_users where username='TEST_USER';
USERNAME
TEMPORARY_TABLESPACE
-----------------------
------------------------------
TEST_USER TEMP2
7.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.
DEFAULT PROFILE SETTING:
Command:
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
*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:
Command:
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:
Command:
ALTER PROFILE APP_PROFILE LIMIT
FAILED_LOGIN_ATTEMPS UNLIMITED;
10. Change
profile of an user:
Command:
SQL> select username,profile
from dba_users where username='TEST_USER';
USERNAME PROFILE
-----------------------
------------------------------
TEST_USER DEFAULT
ALTER USER SCOTT PROFILE
APP_PROFILE;
SQL> select username,profile
from dba_users where username='TEST_USER';
USERNAME PROFILE
-----------------------
------------------------------
TEST_USER 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.
Command:
SQL> select
username,profile,EXPIRY_DATE from dba_users where username='TEST_USER';
USERNAME PROFILE EXPIRY_DATE
-----------------------
----------------------- ---------
TEST_USER 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='TEST_USER';
USERNAME PROFILE EXPIRY_DATE
-----------------------
----------------------- ---------
TEST_USER 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:
Command:
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:
Command:
Grant create any table,alter any
table to TEST_USER;
SQL> select privilege,grantee
from dba_sys_privs where grantee='TEST_USER';
PRIVILEGE GRANTEE
----------------------------------------
---------
CREATE ANY TABLE
ALTER ANY TABLE
14. Revoke
a system privilege from a user:
Command:
REVOKE create any table from TEST_USER;
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:
Command:
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:
Command:
grant insert,update,delete on
SIEBEL.TEST2 to TEST_USER;
-- grant execute on a procedure
grant execute on
SIEBLE.DAILYPROC to TEST_USER;
-- View the granted object
privilege:
select
grantee,owner,table_name,privilege from dba_tab_privs where
grantee='TEST_USER';
17.Revoke
object privilege:
Command:
revoke update on siebel.test2
from TEST_USER;
ROLE:
A role is a collection of
privileges. It allows easier management of privileges.
17.Create a
role:
Command:
create role TEST_ROLE;
18.Grant
privileges to a role:
Command:
grant create session to test_role;
grant select any table to test_role;
grant insert on siebel.test2 to test_role;
-- List of SYSTEM privileges
granted to a ROLE
SQL> select role,privilege
from role_sys_privs where role='TEST_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='TEST_ROLE';
ROLE OWNER TABLE_NAME PRIVILEGE
------------ ------------
------------ ----------------------------------------
TEST_ROLE SIEBEL TEST2 INSERT
19.
Grant role to a User:
Command:
grant dev_role to test_class;
-- List of the user and granted
role:
SQL> select
grantee,GRANTED_ROLE from dba_role_privs where granted_role='TEST_ROLE';
GRANTEE GRANTED_ROLE
------------
-----------------------
SYS TEST_ROLE
TEST_CLASS TEST_ROLE
20. Drop a
user:
Dropping a user will drop all
the objects it owns.
Command:
drop user TEST_CLASS cascade;
21. Drop a
Role:
Command:
Drop role TEST_ROLE;