Create new user from scratch, including Datafiles, Tablespace, Permissions, etc.

--Create IX and TB tablespaces
CREATE TABLESPACE MY_USER_TBS_TB_01
DATAFILE '/oracle_db/db1/db_orainst/MY_USER_TBS_TB_01.dbf' SIZE 10M
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

CREATE TABLESPACE MY_USER_TBS_IX_01
DATAFILE '/oracle_db/db1/db_orainst/MY_USER_TBS_IX_01.dbf' SIZE 10M
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

--Create User
CREATE USER MY_USER IDENTIFIED BY MY_USER;
ALTER USER MY_USER DEFAULT TABLESPACE MY_USER_TBS_TB_01;

or

CREATE USER MY_USER IDENTIFIED BY MY_USER DEFULT TABLESPACE MY_USER_TBS_TB_01 PROFILE USERS_PROFILE;

--Grant quota to create object on Tablespace
ALTER USER MY_USER QUOTA 1000M ON MY_USER_TBS_TB_01;
or
GRANT UNLIMITED TABLESPACE TO MY_USER_TBS_TB_01;

--Grant Roles
GRANT CONNECT, RESOURCE to USER;

--Create DBA Permission User
CREATE USER DBA_ADMIN IDENTIFIED BY DBA_ADMIN;
GRANT CONNECT, RESOURCE to DBA_ADMIN;
GRANT DBA TO DBA_ADMIN;

--Create Regular User
CREATE USER MY_USER IDENTIFIED BY MY_PASS;
GRANT CONNECT, RESOURCE to MY_USER;
GRANT SELECT ANY DICTIONARY TO MY_USER;
GRANT CREATE PUBLIC SYNONYM TO MY_USER;
GRANT DROP PUBLIC SYNONYM TO MY_USER;


--Script to create user
spool cre_user.log append

SET FEEDBACK OFF
SET VERIFY OFF

PROMPT  ************************************************************
PROMPT   Creating the Schema User...
PROMPT  ************************************************************
PROMPT

conn &&adminuser/&&adminpass@&&connectstr
--Drop User if exist
BEGIN
   EXECUTE IMMEDIATE 'DROP USER &&new_user CASCADE';
EXCEPTION
   WHEN OTHERS THEN NULL;
END;
/

--Create user
CREATE USER &&new_user IDENTIFIED BY &&vippass 
DEFAULT TABLESPACE IGT_TABLE 
PROFILE DEFAULT 
ACCOUNT UNLOCK;

GRANT CONNECT, RESOURCE TO &&new_user;
GRANT CREATE PROCEDURE, EXECUTE ANY PROCEDURE TO &&new_user;
GRANT CREATE SNAPSHOT, CREATE TRIGGER, CREATE ANY INDEX TO &&new_user;
GRANT CREATE SYNONYM TO &&new_user;
GRANT UNLIMITED TABLESPACE TO &&new_user;
GRANT QUERY REWRITE TO &&new_user;
GRANT CREATE ANY DIRECTORY TO &&new_user;
GRANT EXECUTE ON DBMS_LOCK TO &&new_user;
GRANT SELECT_CATALOG_ROLE TO &&new_user;

spool off



Create Profile

GRANT EXECUTE ON ora12c_strong_verify_function TO VIPCREATOR;
GRANT EXECUTE ON ora12c_strong_verify_function TO SHDAEMON;

CREATE PROFILE APP_PROF 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 UNLIMITED

         CONNECT_TIME UNLIMITED

         PRIVATE_SGA UNLIMITED

         FAILED_LOGIN_ATTEMPTS UNLIMITED

         PASSWORD_LIFE_TIME UNLIMITED

         PASSWORD_REUSE_TIME UNLIMITED

         PASSWORD_REUSE_MAX UNLIMITED

         PASSWORD_VERIFY_FUNCTION NULL

         PASSWORD_LOCK_TIME UNLIMITED

         PASSWORD_GRACE_TIME UNLIMITED

         INACTIVE_ACCOUNT_TIME UNLIMITED;

ALTER PROFILE APP_PROF 
LIMIT PASSWORD_VERIFY_FUNCTION ora12c_stig_verify_function;

CREATE USER &&vipuser

IDENTIFIED BY &&vippass

DEFAULT TABLESPACE IGT_TABLE

PROFILE APP_PROF ACCOUNT UNLOCK;

ALTER USER &&vipuser PROFILE APP_PROF;

See current profiles and their resources:

SELECT DISTINCT owner, name 
 FROM DBA_SOURCE 
WHERE type = 'FUNCTION' 
  AND name LIKE 'ORA12C_STIG_VERIFY_FUNCTION%';

SELECT profile, resource_name, limit
  FROM DBA_PROFILES 
 WHERE profile = 'APP_PROF';

PROFILE        RESOURCE_NAME                  LIMIT
-------------- ------------------------------ ------------------------------
APP_PROF       COMPOSITE_LIMIT                UNLIMITED
APP_PROF       SESSIONS_PER_USER              UNLIMITED
APP_PROF       CPU_PER_SESSION                UNLIMITED
APP_PROF       CPU_PER_CALL                   UNLIMITED
APP_PROF       LOGICAL_READS_PER_SESSION      UNLIMITED
APP_PROF       LOGICAL_READS_PER_CALL         UNLIMITED
APP_PROF       IDLE_TIME                      UNLIMITED
APP_PROF       CONNECT_TIME                   UNLIMITED
APP_PROF       PRIVATE_SGA                    UNLIMITED
APP_PROF       FAILED_LOGIN_ATTEMPTS          UNLIMITED
APP_PROF       PASSWORD_LIFE_TIME             UNLIMITED
APP_PROF       PASSWORD_REUSE_TIME            UNLIMITED
APP_PROF       PASSWORD_REUSE_MAX             UNLIMITED
APP_PROF       PASSWORD_VERIFY_FUNCTION       ORA12C_STIG_VERIFY_FUNCTION
APP_PROF       PASSWORD_LOCK_TIME             UNLIMITED
APP_PROF       PASSWORD_GRACE_TIME            UNLIMITED
APP_PROF       INACTIVE_ACCOUNT_TIME          UNLIMITED

--Using Substitution Variable
SET FEEDBACK OFF 
SET VERIFY OFF

DEFINE vipuser = 'SH_DBA';
DEFINE vippass = 'SH_DBA';

--Create user
CREATE USER &&vipuser IDENTIFIED BY &&vippass DEFAULT TABLESPACE IGT_TABLE PROFILE DEFAULT ACCOUNT UNLOCK;
--CREATE USER &&vipuser IDENTIFIED BY &&vippass DEFAULT TABLESPACE IGT_TABLE PROFILE APP_PROF ACCOUNT UNLOCK;
GRANT CREATE PROCEDURE, EXECUTE ANY PROCEDURE, CONNECT, CREATE SNAPSHOT, CREATE TRIGGER, CREATE ANY INDEX TO &&VIPUSER;
GRANT CREATE SYNONYM TO &&VIPUSER;
GRANT UNLIMITED TABLESPACE TO &&VIPUSER;
GRANT QUERY REWRITE TO &&VIPUSER ;
GRANT CREATE ANY DIRECTORY TO &&VIPUSER;
GRANT EXECUTE ON DBMS_LOCK TO &&VIPUSER;
GRANT SELECT_CATALOG_ROLE TO &&VIPUSER;
GRANT CHANGE NOTIFICATION TO &&VIPUSER';

GRANT DBA TO &&VIPUSER;

ORA12C_STIG_VERIFY_FUNCTION: