Pages

Thursday, May 4, 2017

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:

No comments:

Post a Comment