Create user script
select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,PROFILE from dba_users where USERNAME='MARS';
create user MARS identified by Z96bix#123;
CREATE USER MARS IDENTIFIED BY XXXXXXX DEFAULT TABLESPACE USERS PROFILE CUSTOMER;
Change User Default Tablespace
alter user usr1 default tablespace example;
CREATE USER SCOTT
IDENTIFIED BY xyz123##
DEFAULT TABLESPACE ARSYSTEM
TEMPORARY TABLESPACE ARTMPSPC
PROFILE SCOTT_USER
ACCOUNT UNLOCK;
-- 4 Roles for SCOTT
GRANT ARADMIN_READ_ONLY TO SCOTT;
GRANT AROLE TO SCOTT;
GRANT DATA_MANAGER TO SCOTT WITH ADMIN OPTION;
GRANT SELECT_CATALOG_ROLE TO SCOTT;
ALTER USER SCOTT DEFAULT ROLE ALL;
ALTER USER SCOTT IDENTIFIED BY "Sc123!" ACCOUNT LOCK PASSWORD EXPIRE;
(This will ensure scott the Scott user password will changed and on successfull login it will prompt to change the password and the account will be lock
To make the password as expire:
alter user DATASAFE password expire;
To set the same password
alter user MARS identified by values 'S:6854B22E744A65D6C10456319075DC87CA65A3BAF93C6126F539818AC2E3;T:196';
To unlock the account:
alter user dev_class account lock;
alter user dev_class account unlock;
To check when the password of a user has changed:
select name, to_char(ptime,'DD/MM/YY HH24:MIS') as pw_last_changed from sys.user$
Query to find the user password change and account lock history:
SELECT NAME, CTIME, PTIME, LTIME, LCOUNT FROM SYS.USER$ WHERE NAME='CT2467408';
-- 7 System Privileges for SCOTT
GRANT CREATE ANY SNAPSHOT TO SCOTT;
GRANT CREATE ANY TABLE TO SCOTT;
GRANT CREATE DATABASE LINK TO SCOTT;
GRANT CREATE ROLE TO SCOTT;
GRANT CREATE TABLE TO SCOTT;
GRANT ON COMMIT REFRESH TO SCOTT;
GRANT UNLIMITED TABLESPACE TO SCOTT;
GRANT CONNECT TO MARS;
GRANT RESOURCE TO MARS;
GRANT CREATE SESSION TO MARS;
GRANT SELECT ANY DICTIONARY TO MARS;
CREATE PROFILE SERVICE_ACCOUNT
LIMIT
COMPOSITE_LIMIT DEFAULT
SESSIONS_PER_USER DEFAULT
CPU_PER_SESSION DEFAULT
CPU_PER_CALL DEFAULT
LOGICAL_READS_PER_SESSION DEFAULT
LOGICAL_READS_PER_CALL DEFAULT
IDLE_TIME DEFAULT
CONNECT_TIME DEFAULT
PRIVATE_SGA DEFAULT
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_REUSE_TIME 31536000/86400
PASSWORD_REUSE_MAX 10
PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION
PASSWORD_LOCK_TIME 432000/86400
PASSWORD_GRACE_TIME UNLIMITED
INACTIVE_ACCOUNT_TIME DEFAULT
/
alter user MARS profile new_profile;
alter profile PROFILE_DBA_USER limit PASSWORD_VERIFY_FUNCTION "PWD_VERIFY_FUNCTION_12C";
Tablespace Quotas for REPORTER:
ALTER USER REPORTER QUOTA UNLIMITED ON REPASSM_TEMP;
ALTER USER REPORTER QUOTA 50M ON REPHOME;
To get metadata of a user:
set heading off;
set echo off;
SET TRIMSPOOL ON
SET FEEDBACK OFF
set lines 1000 pages 4000;
set long 1000000
select dbms_metadata.get_ddl('USER','T466464') from dual;
SELECT DBMS_METADATA.GET_DDL('PROFILE','DEFAULT') from dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','T466464') from dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','VAIBHAVE') from dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','SUB') from dual;
SELECT dbms_metadata.get_ddl('ROLE','SUB') from dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','RESOURCE') from dual;
Enjoy the work and do like me if you like this article!!!