User Management In Oracle 223 views 2 min , 25 sec read 0
User is basically used to connect to database. d atabase. All db objects like table,index,view etc can be created under that user.In Oracle, users and schemas are essentially the same thing. You can consider that a user is the account you use to connect to a database, and a schema is the set of objects (tables, views, etc.) that belong to that t hat account.
1. Create a user: 1 2 3 create user DEV_CLASS identified by DEV_CLASS#1234 4 PROFILE DEFAULT 5 DEFAULT TABLESPACE USERS 6 TEMPORARY TABLESPACE TEMP; 7
Minimum privilege required to connect to a database is create session 1 2 3 grant create session to DEV_CLASS; 4
2. Change password of a user: 1 2 3 alter user DEV_CLASS identified by DEV_CLASS#91234; 4
3. Lock/unlock a user 1 2 3 alter user dev_class account lock; 4 5 alter user dev_class account unlock; 6
4. Make a user password expiry: When we make a user id expiry, then when the user does d oes login, it will prompt him to set a new password. 1 2
3 alter user dev_class account expire; 4
5. Changing default tablespace of a user: 1 2 3 select username,default_tablespace from d ba_users where u sername='DEV_CLASS'; 4 5 USERNAME
DEFAULT_TABLESPACE
6 ----------------------- -----------------------------7 DEV_CLASS
USERS
8 9 alter user DEV_CLASS default tablespace DATATS; 10 11 select username,default_tablespace from d ba_users where u sername='DEV_CLASS'; 12 13 USERNAME
DEFAULT_TABLESPACE
14 ----------------------- -----------------------------15 DEV_CLASS
DATATS
16
6. Changing default TEMP tablespace of a user: 1 2 3 SQL> select username,TEMPORARY_TABLESPACE from dba_users where username='DEV_CLASS'; 4 USERNAME TEMPORARY_TABLESPACE 5 ----------------------- -----------------------------6 DEV_CLASS TEMP 7 8 alter user DEV_CLASS temporary tablespace TEMP2; 9 10 SQL> select username,TEMPORARY_TABLESPACE from dba_users where u sername='DEV_CLASS'; 11 12 USERNAME TEMPORARY_TABLESPACE 13 ----------------------- -----------------------------14 DEV_CLASS TEMP2 15
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: 1 2 3 col limit for a12 4 col profile for a14
5 set lines 200 6 set pagesize 200 7 select profile,resource_name,RESOURCE_TYPE,limit from dba_profiles where profile='DEFAULT'; 8 9 PROFILE
RESOURCE_NAME
RESOURCE LIMIT
10 -------------- -------------------------------- -------- -----------11 DEFAULT
COMPOSITE_LIMIT
KERNEL UNLIMITED
12 DEFAULT
SESSIONS_PER_USER
13 DEFAULT
CPU_PER_SESSION
14 DEFAULT
CPU_PER_CALL
15 DEFAULT
LOGICAL_READS_PER_SESSION
16 DEFAULT
LOGICAL_READS_PER_CALL
17 DEFAULT
IDLE_TIME
18 DEFAULT
CONNECT_TIME
19 DEFAULT
PRIVATE_SGA
20 DEFAULT
FAILED_LOGIN_ATTEMPTS
21 DEFAULT
PASSWORD_LIFE_TIME
22 DEFAULT
PASSWORD_REUSE_TIME
PASSWORD UNLIMITED
23 DEFAULT
PASSWORD_REUSE_MAX
PASSWORD UNLIMITED
24 DEFAULT
PASSWORD_VERIFY_FUNCTION
25 DEFAULT
PASSWORD_LOCK_TIME
26 DEFAULT
PASSWORD_GRACE_TIME
KERNEL UNLIMITED KERNEL UNLIMITED KERNEL UNLIMITED KERNEL UNLIMITED KERNEL UNLIMITED
KERNEL UNLIMITED KERNEL UNLIMITED KERNEL UNLIMITED PASSWORD 10 PASSWORD 180
PASSWORD NULL PASSWORD 1 PASSWORD 7
27
*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 se ssion,
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: 1 2 3 CREATE PROFILE "APP_PROFILE" 4
LIMIT
5
COMPOSITE_LIMIT UNLIMITED
6
SESSIONS_PER_USER UNLIMITED
7
CPU_PER_SESSION UNLIMITED
8
CPU_PER_CALL UNLIMITED
9
LOGICAL_READS_PER_SESSION UNLIMITED
10
LOGICAL_READS_PER_CALL UNLIMITED
11
IDLE_TIME 90
12
CONNECT_TIME UNLIMITED
13
PRIVATE_SGA UNLIMITED
14
FAILED_LOGIN_ATTEMPTS 10
15
PASSWORD_LIFE_TIME 180
16
PASSWORD_REUSE_TIME UNLIMITED
17
PASSWORD_REUSE_MAX UNLIMITED
18
PASSWORD_VERIFY_FUNCTION NULL
19
PASSWORD_LOCK_TIME UNLIMITED
20
PASSWORD_GRACE_TIME UNLIMITED;
21
9. Alter a profile: 1 2 3 ALTER PROFILE APP_PROFILE LIMIT FAILED_LOGIN_ATTEMPS UNLIMITED; 4
10. Change profile of an user: 1 2 3 SQL> select username,profile from dba_users where username='DEV_CLASS'; 4 USERNAME PROFILE 5 ----------------------- -----------------------------6 DEV_CLASS DEFAULT 7 8 ALTER USER SCOTT PROFILE APP_PROFILE; 9 10 SQL> select username,profile from dba_users where username='DEV_CLASS'; 11
12 USERNAME PROFILE 13 ----------------------- -----------------------------14 DEV_CLASS APP_PROFILE 15
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. 1 2 3 SQL> select username,profile,EXPIRY_DATE from dba_users where username='DEV_CLASS'; 4 USERNAME PROFILE EXPIRY_DATE 5 ----------------------- ----------------------- --------6 DEV_CLASS APP_PROFILE 16-AUG-17 7 8 ALTER PROFILE APP_PROFILE LIMIT PASSWORD_LIFE_TIME UNLIMITED; 9 10 SQL> select username,profile,EXPIRY_DATE from dba_users where username='DEV_CLASS'; 11 12 USERNAME PROFILE EXPIRY_DATE 13 ----------------------- ----------------------- --------14 DEV_CLASS APP_PROFILE 15
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: 1 2 3
SQL>select distinct privilege from dba_sys_privs;
4 5
PRIVILEGE
6
----------------------------------------
7
CREATE SESSION
8
CREATE OPERATOR
9
CREATE VIEW
10 CREATE ANY PROCEDURE 11 CREATE DATABASE LINK 12 DEQUEUE ANY QUEUE 13 DEBUG ANY P ROCEDURE 14 CREATE PUBLIC SYNONYM 15 SELECT ANY TRANSACTION 16 READ ANY TABLE 17 CREATE ASSEMBLY 18 EXECUTE ANY INDEXTYPE 19 CREATE ANY TYPE 20 ANALYZE ANY 21 DROP PUBLIC SYNONYM 22 AUDIT SYSTEM 23 EXECUTE ANY ASSEMBLY 24 CREATE ANY EDITION 25 ADMINISTER ANY SQL TUNING SET 26 DROP ANY RULE SET 27 CREATE ANY EVALUATION CONTEXT 28 ADMINISTER DATABASE TRIGGER 29 ADMINISTER RESOURCE MANAGER 30 GRANT ANY PRIVILEGE 31 ALTER RESOURCE COST 32 ALTER ANY TRIGGER 33 DROP ANY SYNONYM 34 CREATE USER 35 CREATE SQL TRANSLATION PROFILE 36 EM EXPRESS CONNECT 37 CREATE ANY TRIGGER 38 EXEMPT REDACTION POLICY 39 CREATE DIMENSION 40 CREATE RULE SET 41 EXECUTE ANY EVALUATION CONTEXT 42 ALTER ANY OUTLINE 43 UNDER ANY TYPE 44 ALTER ANY ROLE 45 CREATE ANY MINING MODEL 46 DROP ANY OUTLINE 47 ALTER ANY INDEX 48 UPDATE ANY TABLE 49 CREATE TABLESPACE 50 USE ANY SQL TRANSLATION PROFILE 51 DROP ANY VIEW 52 CREATE ANY SQL TRANSLATION PROFILE 53 BECOME USER
54 DROP ANY MEASURE FOLDER 55 CREATE ANY CUBE 56 CREATE ANY OUTLINE 57 COMMENT ANY MINING MODEL 58 ALTER ANY INDEXTYPE 59 DROP PROFILE 60 CREATE PROCEDURE 61 CREATE SEQUENCE 62 CREATE JOB 63 EXEMPT ACCESS POLICY 64 QUERY REWRITE 65 EXECUTE ANY RULE SET 66 CREATE PLUGGABLE DATABASE 67 ALTER ANY CUBE 68 ALTER ANY RULE SET 69 UNDER ANY VIEW 70 DROP ANY PROCEDURE 71 CREATE ROLE 72 CREATE ANY TABLE 73 RESTRICTED SESSION 74 ALTER ANY MEASURE FOLDER 75 ADVISOR 76 IMPORT FULL DATABASE 77 DROP ANY TRIGGER 78 ALTER ANY PROCEDURE 79 SELECT ANY SEQUENCE 80 CREATE ANY CONTEXT 81 UNDER ANY TABLE 82 ALTER PROFILE 83 FORCE TRANSACTION 84 DROP ANY MINING MODEL 85 CREATE ANY OPERATOR 86 CREATE PUBLIC DATABASE LINK 87 MANAGE ANY FILE GROUP 88 MANAGE TABLESPACE 89 CREATE CUBE DIMENSION 90 UNLIMITED TABLESPACE 91 SELECT ANY TABLE 92 CREATE EVALUATION CONTEXT 93 ON COMMIT REFRESH 94 CREATE ANY INDEX 95 EXECUTE ANY PROGRAM 96 ALTER ANY CUBE BUILD PROCESS 97 CREATE ANY MEASURE FOLDER 98 EXECUTE ASSEMBLY 99 CREATE ANY SQL PROFILE 100 ALTER ANY TYPE
101 CREATE PROFILE 102 EXECUTE ANY PROCEDURE 103 CREATE ANY CLUSTER 104 CREATE ANY ASSEMBLY 105 CREATE ANY RULE 106 EXECUTE ANY TYPE 107 ALTER ANY CLUSTER 108 DROP ANY CUBE 109 DROP PUBLIC DATABASE LINK 110 SELECT ANY MEASURE FOLDER 111 REDEFINE ANY TABLE 112 SELECT ANY CUBE 113 CREATE ANY INDEXTYPE 114 CREATE ANY CUBE DIMENSION 115 EXEMPT DDL REDACTION POLICY 116 MANAGE SCHEDULER 117 ALTER SESSION 118 CREATE TRIGGER 119 CREATE MATERIALIZED VIEW 120 ALTER ANY SEQUENCE 121 EXEMPT IDENTITY POLICY 122 CREATE ANY CREDENTIAL 123 SET CONTAINER 124 GLOBAL QUERY REWRITE 125 ALTER ANY LIBRARY 126 GRANT ANY ROLE 127 ALTER USER 128 CREATE MEASURE FOLDER 129 UPDATE ANY CUBE 130 READ ANY FILE GROUP 131 GRANT ANY OBJECT PRIVILEGE 132 DROP ANY OPERATOR 133 CREATE CREDENTIAL 134 CHANGE NOTIFICATION 135 CREATE ANY SYNONYM 136 INSERT ANY TABLE 137 EXEMPT DML REDACTION POLICY 138 EXECUTE ANY RULE 139 INSERT ANY MEASURE FOLDER 140 DROP ANY CUBE DIMENSION 141 ALTER ANY ASSEMBLY 142 LOGMINING 143 CREATE ANY VIEW 144 CREATE TYPE 145 FLASHBACK ARCHIVE ADMINISTER 146 ADMINISTER SQL MANAGEMENT OBJECT 147 ALTER ANY MINING MODEL
148 SELECT ANY MINING MODEL 149 CREATE EXTERNAL JOB 150 DROP ANY EVALUATION CONTEXT 151 CREATE LIBRARY 152 DROP ANY SQL TRANSLATION P ROFILE 153 CREATE MINING MODEL 154 DROP ANY CONTEXT 155 MANAGE ANY QUEUE 156 DROP ANY DIMENSION 157 CREATE ANY DIMENSION 158 CREATE ANY LIBRARY 159 DROP ANY MATERIALIZED VIEW 160 CREATE ANY MATERIALIZED VIEW 161 ALTER DATABASE 162 DROP ANY ROLE 163 LOCK ANY TABLE 164 DROP USER 165 DROP TABLESPACE 166 MERGE ANY VIEW 167 DROP ANY TYPE 168 COMMENT ANY TABLE 169 ALTER TABLESPACE 170 CREATE CUBE 171 ALTER ANY SQL PROFILE 172 DROP ANY INDEXTYPE 173 ALTER ROLLBACK SEGMENT 174 DROP ANY CUBE BUILD PROCESS 175 CREATE ANY CUBE BUILD PROCESS 176 DELETE ANY CUBE DIMENSION 177 ANALYZE ANY DICTIONARY 178 CREATE TABLE 179 ALTER ANY TABLE 180 SELECT ANY DICTIONARY 181 CREATE CLUSTER 182 DEBUG CONNECT SESSION 183 CREATE INDEXTYPE 184 INHERIT ANY PRIVILEGES 185 DROP ANY SQL PROFILE 186 CREATE ANY DIRECTORY 187 DROP ANY INDEX 188 ENQUEUE ANY QUEUE 189 DROP ANY CLUSTER 190 SELECT ANY CUBE BUILD PROCESS 191 ADMINISTER KEY MANAGEMENT 192 ALTER ANY SQL TRANSLATION PROFILE 193 DROP ANY EDITION 194 CREATE ROLLBACK SEGMENT
195 SELECT ANY CUBE DIMENSION 196 ALTER ANY EVALUATION CONTEXT 197 FORCE ANY TRANSACTION 198 INSERT ANY CUBE DIMENSION 199 ALTER ANY OPERATOR 200 EXECUTE ANY LIBRARY 201 ALTER ANY MATERIALIZED VIEW 202 ALTER ANY CUBE DIMENSION 203 CREATE SYNONYM 204 FLASHBACK ANY TABLE 205 CREATE RULE 206 EXECUTE ANY CLASS 207 CREATE ANY SEQUENCE 208 ALTER SYSTEM 209 UPDATE ANY CUBE DIMENSION 210 UPDATE ANY CUBE BUILD PROCESS 211 CREATE CUBE BUILD PROCESS 212 DROP ANY ASSEMBLY 213 ADMINISTER SQL TUNING SET 214 EXECUTE ANY OPERATOR 215 DROP ANY LIBRARY 216 AUDIT ANY 217 DELETE ANY TABLE 218 RESUMABLE 219 DROP ANY TABLE 220 ALTER ANY EDITION 221 EXPORT FULL DATABASE 222 DROP ANY DIRECTORY 223 DROP ANY SEQUENCE 224 DROP ROLLBACK SEGMENT 225 CREATE ANY JOB 226 BACKUP ANY TABLE 227 DELETE ANY MEASURE FOLDER 228 MANAGE FILE GROUP 229 DROP ANY RULE 230 ALTER ANY DIMENSION 231 CREATE ANY RULE SET 232 ALTER ANY RULE 233
13.Grant a system privilege to a user: 1 2 3 Grant create any table,alter any table to DEV_CLASS; 4 5 SQL> select privilege,grantee from dba_sys_privs where grantee='DEV_CLASS'; 6 7 PRIVILEGE
GRANTEE
8 ---------------------------------------- --------9 CREATE ANY TABLE 10 ALTER ANY TABLE
DEV_CLASS DEV_CLASS
11
14. Revoke a system privilege from a user: 1 2 3 REVOKE create any table from d ev_class; 4
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: 1 2 3 SQL> select distinct privilege from DBA_TAB_PRIVS; 4 5 PRIVILEGE 6 ---------------------------------------7 EXECUTE 8 SELECT 9 INSERT 10 INDEX 11 DEQUEUE 12 USE 13 QUERY REWRITE 14 READ 15 ON COMMIT REFRESH 16 REFERENCES 17 INHERIT PRIVILEGES 18 DEBUG 19 ALTER 20 UPDATE 21 WRITE 22 FLASHBACK 23 DELETE 24
16.Grant object privilege: 1 2 3 grant insert,update,delete on SIEBEL.TEST2 to DEV_CLASS; 4 5 -- grant execute on a p rocedure
6 7 grant execute on S IEBLE.DAILYPROC to DEV_CLASS; 8 9 -- View the granted object privilege: 10 11 select grantee,owner,table_name,privilege from db a_tab_privs where grantee='DEV_CLASS'; 12
17.Revoke object privilege: 1 2 3 revoke update on siebel.test2 from DEV_CLASS; 4
ROLE: A role is a collection of privileges. It allows easier management of privileges.
17.Create a role: 1 2 3 create role DEV_ROLE; 4
18.Grant privileges to a role: 1 2 3 grant create session to dev_role; 4 grant select any table to dev_role; 5 grant insert on siebel.test2 to dev_role; 6 7 -- List of SYSTEM privileges granted to a ROLE 8 9 SQL> select role,privilege from role_sys_privs where role='DEV_ROLE'; 10 11 ROLE
PRIVILEGE
12 ------------ ---------------------------------------13 DEV_ROLE
CREATE SESSION
14 DEV_ROLE
SELECT ANY TABLE
15 16 -- List of OBJECT privileges granted to ROLE; 17 18 SQL> select role,owner,table_name,privilege from role_tab_privs where role='DEV_ROLE'; 19 20 ROLE
OWNER
TABLE_NAME PRIVILEGE
21 ------------ ------------ ------------ ---------------------------------------22 DEV_ROLE 23
SIEBEL
TEST2
INSERT
19. Grant role to a User: 1 2 3 grant dev_role to dev_class; 4 5 -- List of the user and granted role: 6 7 SQL> select grantee,GRANTED_ROLE from dba_role_privs where granted_role='DEV_ROLE'; 8 9 GRANTEE
GRANTED_ROLE
10 ------------ ----------------------11 SYS
DEV_ROLE
12 DEV_CLASS
DEV_ROLE
13
20. Drop a user: Dropping a user will drop all the objects it owns. 1 2 3 drop user DEV_CLASS cascade; 4
21. Drop a Role: 1 2 3 Drop role DEV_ROLE; 4
