建立用户名脚本
DROP USER saas CASCADE;
CREATE USER saas
IDENTIFIED BY VALUES '7B8CE7ECF799061D'
DEFAULT TABLESPACE BJDVDATA
TEMPORARY TABLESPACE BJDVTEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
-- 4 Roles for saas
GRANT CONNECT TO saas WITH ADMIN OPTION;
GRANT DBA TO saas WITH ADMIN OPTION;
GRANT EXP_FULL_DATABASE TO saas WITH ADMIN OPTION;
GRANT IMP_FULL_DATABASE TO saas WITH ADMIN OPTION;
ALTER USER saas DEFAULT ROLE ALL;
-- 147 System Privileges for saas
GRANT ADMINISTER DATABASE TRIGGER TO saas WITH ADMIN OPTION;
BEGIN
SYS.DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_PRIVILEGE
(GRANTEE_NAME => 'saas',
PRIVILEGE_NAME => 'ADMINISTER_RESOURCE_MANAGER',
ADMIN_OPTION => TRUE);
END;
/
GRANT ADMINISTER ENCRYPTION TO saas WITH ADMIN OPTION;
GRANT ALTER ANY CLUSTER TO saas WITH ADMIN OPTION;
GRANT ALTER ANY DIMENSION TO saas WITH ADMIN OPTION;
BEGIN
SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
PRIVILEGE => SYS.DBMS_RULE_ADM.ALTER_ANY_EVALUATION_CONTEXT,
GRANTEE => 'saas',
GRANT_OPTION => TRUE);
END;
/
GRANT ALTER ANY INDEX TO saas WITH ADMIN OPTION;
GRANT ALTER ANY INDEXTYPE TO saas WITH ADMIN OPTION;
GRANT ALTER ANY LIBRARY TO saas WITH ADMIN OPTION;
GRANT ALTER ANY OPERATOR TO saas WITH ADMIN OPTION;
GRANT ALTER ANY OUTLINE TO saas WITH ADMIN OPTION;
GRANT ALTER ANY PROCEDURE TO saas WITH ADMIN OPTION;
GRANT ALTER ANY ROLE TO saas WITH ADMIN OPTION;
BEGIN
SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
PRIVILEGE => SYS.DBMS_RULE_ADM.ALTER_ANY_RULE,
GRANTEE => 'saas',
GRANT_OPTION => TRUE);
END;
/
BEGIN
SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
PRIVILEGE => SYS.DBMS_RULE_ADM.ALTER_ANY_RULE_SET,
GRANTEE => 'saas',
GRANT_OPTION => TRUE);
END;
/
GRANT ALTER ANY ENCRYPTION PROFILE TO saas WITH ADMIN OPTION;
GRANT ALTER ANY SEQUENCE TO saas WITH ADMIN OPTION;
GRANT ALTER ANY SNAPSHOT TO saas WITH ADMIN OPTION;
GRANT ALTER ANY TABLE TO saas WITH ADMIN OPTION;
GRANT ALTER ANY TRIGGER TO saas WITH ADMIN OPTION;
GRANT ALTER ANY TYPE TO saas WITH ADMIN OPTION;
GRANT ALTER DATABASE TO saas WITH ADMIN OPTION;
GRANT ALTER PROFILE TO saas WITH ADMIN OPTION;
GRANT ALTER RESOURCE COST TO saas WITH ADMIN OPTION;
GRANT ALTER ROLLBACK SEGMENT TO saas WITH ADMIN OPTION;
GRANT ALTER SESSION TO saas WITH ADMIN OPTION;
GRANT ALTER SYSTEM TO saas WITH ADMIN OPTION;
GRANT ALTER TABLESPACE TO saas WITH ADMIN OPTION;
GRANT ALTER USER TO saas WITH ADMIN OPTION;
GRANT ANALYZE ANY TO saas WITH ADMIN OPTION;
GRANT AUDIT ANY TO saas WITH ADMIN OPTION;
GRANT AUDIT SYSTEM TO saas WITH ADMIN OPTION;
GRANT BACKUP ANY TABLE TO saas WITH ADMIN OPTION;
GRANT BECOME USER TO saas WITH ADMIN OPTION;
GRANT COMMENT ANY TABLE TO saas WITH ADMIN OPTION;
GRANT CREATE ANY CLUSTER TO saas WITH ADMIN OPTION;
GRANT CREATE ANY CONTEXT TO saas WITH ADMIN OPTION;
GRANT CREATE ANY DIMENSION TO saas WITH ADMIN OPTION;
GRANT CREATE ANY DIRECTORY TO saas WITH ADMIN OPTION;
BEGIN
SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
PRIVILEGE => SYS.DBMS_RULE_ADM.CREATE_ANY_EVALUATION_CONTEXT,
GRANTEE => 'saas',
GRANT_OPTION => TRUE);
END;
/
GRANT CREATE ANY INDEX TO saas WITH ADMIN OPTION;
GRANT CREATE ANY INDEXTYPE TO saas WITH ADMIN OPTION;
GRANT CREATE ANY LIBRARY TO saas WITH ADMIN OPTION;
GRANT CREATE ANY OPERATOR TO saas WITH ADMIN OPTION;
GRANT CREATE ANY OUTLINE TO saas WITH ADMIN OPTION;
GRANT CREATE ANY PROCEDURE TO saas WITH ADMIN OPTION;
BEGIN
SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
PRIVILEGE => SYS.DBMS_RULE_ADM.CREATE_ANY_RULE,
GRANTEE => 'saas',
GRANT_OPTION => TRUE);
END;
/
BEGIN
SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
PRIVILEGE => SYS.DBMS_RULE_ADM.CREATE_ANY_RULE_SET,
GRANTEE => 'saas',
GRANT_OPTION => TRUE);
END;
/
GRANT CREATE ANY ENCRYPTION PROFILE TO saas WITH ADMIN OPTION;
GRANT CREATE ANY SEQUENCE TO saas WITH ADMIN OPTION;
GRANT CREATE ANY SNAPSHOT TO saas WITH ADMIN OPTION;
GRANT CREATE ANY SYNONYM TO saas WITH ADMIN OPTION;
GRANT CREATE ANY TABLE TO saas WITH ADMIN OPTION;
GRANT CREATE ANY TRIGGER TO saas WITH ADMIN OPTION;
GRANT CREATE ANY TYPE TO saas WITH ADMIN OPTION;
GRANT CREATE ANY VIEW TO saas WITH ADMIN OPTION;
GRANT CREATE CLUSTER TO saas WITH ADMIN OPTION;
GRANT CREATE DATABASE LINK TO saas WITH ADMIN OPTION;
GRANT CREATE DIMENSION TO saas WITH ADMIN OPTION;
BEGIN
SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
PRIVILEGE => SYS.DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_OBJ,
GRANTEE => 'saas',
GRANT_OPTION => TRUE);
END;
/
GRANT CREATE INDEXTYPE TO saas WITH ADMIN OPTION;
GRANT CREATE LIBRARY TO saas WITH ADMIN OPTION;
GRANT CREATE OPERATOR TO saas WITH ADMIN OPTION;
GRANT CREATE PROCEDURE TO saas WITH ADMIN OPTION;
GRANT CREATE PROFILE TO saas WITH ADMIN OPTION;
GRANT CREATE PUBLIC DATABASE LINK TO saas WITH ADMIN OPTION;
GRANT CREATE PUBLIC SYNONYM TO saas WITH ADMIN OPTION;
GRANT CREATE ROLE TO saas WITH ADMIN OPTION;
GRANT CREATE ROLLBACK SEGMENT TO saas WITH ADMIN OPTION;
BEGIN
SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
PRIVILEGE => SYS.DBMS_RULE_ADM.CREATE_RULE_OBJ,
GRANTEE => 'saas',
GRANT_OPTION => TRUE);
END;
/
BEGIN
SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
PRIVILEGE => SYS.DBMS_RULE_ADM.CREATE_RULE_SET_OBJ,
GRANTEE => 'saas',
GRANT_OPTION => TRUE);
END;
/
GRANT CREATE ENCRYPTION PROFILE TO saas WITH ADMIN OPTION;
GRANT CREATE SEQUENCE TO saas WITH ADMIN OPTION;
GRANT CREATE SESSION TO saas WITH ADMIN OPTION;
GRANT CREATE SNAPSHOT TO saas WITH ADMIN OPTION;
GRANT CREATE SYNONYM TO saas WITH ADMIN OPTION;
GRANT CREATE TABLE TO saas WITH ADMIN OPTION;
GRANT CREATE TABLESPACE TO saas WITH ADMIN OPTION;
GRANT CREATE TRIGGER TO saas WITH ADMIN OPTION;
GRANT CREATE TYPE TO saas WITH ADMIN OPTION;
GRANT CREATE USER TO saas WITH ADMIN OPTION;
GRANT CREATE VIEW TO saas WITH ADMIN OPTION;
GRANT DEBUG ANY PROCEDURE TO saas WITH ADMIN OPTION;
GRANT DEBUG CONNECT SESSION TO saas WITH ADMIN OPTION;
GRANT DELETE ANY TABLE TO saas WITH ADMIN OPTION;
BEGIN
SYS.DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE (
PRIVILEGE => 'DEQUEUE_ANY',
GRANTEE => 'saas',
ADMIN_OPTION => TRUE);
END;
/
GRANT DROP ANY CLUSTER TO saas WITH ADMIN OPTION;
GRANT DROP ANY CONTEXT TO saas WITH ADMIN OPTION;
GRANT DROP ANY DIMENSION TO saas WITH ADMIN OPTION;
GRANT DROP ANY DIRECTORY TO saas WITH ADMIN OPTION;
BEGIN
SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
PRIVILEGE => SYS.DBMS_RULE_ADM.DROP_ANY_EVALUATION_CONTEXT,
GRANTEE => 'saas',
GRANT_OPTION => TRUE);
END;
/
GRANT DROP ANY INDEX TO saas WITH ADMIN OPTION;
GRANT DROP ANY INDEXTYPE TO saas WITH ADMIN OPTION;
GRANT DROP ANY LIBRARY TO saas WITH ADMIN OPTION;
GRANT DROP ANY OPERATOR TO saas WITH ADMIN OPTION;
GRANT DROP ANY OUTLINE TO saas WITH ADMIN OPTION;
GRANT DROP ANY PROCEDURE TO saas WITH ADMIN OPTION;
GRANT DROP ANY ROLE TO saas WITH ADMIN OPTION;
BEGIN
SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
PRIVILEGE => SYS.DBMS_RULE_ADM.DROP_ANY_RULE,
GRANTEE => 'saas',
GRANT_OPTION => TRUE);
END;
/
BEGIN
SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
PRIVILEGE => SYS.DBMS_RULE_ADM.DROP_ANY_RULE_SET,
GRANTEE => 'saas',
GRANT_OPTION => TRUE);
END;
/
GRANT DROP ANY ENCRYPTION PROFILE TO saas WITH ADMIN OPTION;
GRANT DROP ANY SEQUENCE TO saas WITH ADMIN OPTION;
GRANT DROP ANY SNAPSHOT TO saas WITH ADMIN OPTION;
GRANT DROP ANY SYNONYM TO saas WITH ADMIN OPTION;
GRANT DROP ANY TABLE TO saas WITH ADMIN OPTION;
GRANT DROP ANY TRIGGER TO saas WITH ADMIN OPTION;
GRANT DROP ANY TYPE TO saas WITH ADMIN OPTION;
GRANT DROP ANY VIEW TO saas WITH ADMIN OPTION;
GRANT DROP PROFILE TO saas WITH ADMIN OPTION;
GRANT DROP PUBLIC DATABASE LINK TO saas WITH ADMIN OPTION;
GRANT DROP PUBLIC SYNONYM TO saas WITH ADMIN OPTION;
GRANT DROP ROLLBACK SEGMENT TO saas WITH ADMIN OPTION;
GRANT DROP TABLESPACE TO saas WITH ADMIN OPTION;
GRANT DROP USER TO saas WITH ADMIN OPTION;
BEGIN
SYS.DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE (
PRIVILEGE => 'ENQUEUE_ANY',
GRANTEE => 'saas',
ADMIN_OPTION => TRUE);
END;
/
BEGIN
SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
PRIVILEGE => SYS.DBMS_RULE_ADM.EXECUTE_ANY_EVALUATION_CONTEXT,
GRANTEE => 'saas',
GRANT_OPTION => TRUE);
END;
/
GRANT EXECUTE ANY INDEXTYPE TO saas WITH ADMIN OPTION;
GRANT EXECUTE ANY LIBRARY TO saas WITH ADMIN OPTION;
GRANT EXECUTE ANY OPERATOR TO saas WITH ADMIN OPTION;
GRANT EXECUTE ANY PROCEDURE TO saas WITH ADMIN OPTION;
BEGIN
SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
PRIVILEGE => SYS.DBMS_RULE_ADM.EXECUTE_ANY_RULE,
GRANTEE => 'saas',
GRANT_OPTION => TRUE);
END;
/
BEGIN
SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
PRIVILEGE => SYS.DBMS_RULE_ADM.EXECUTE_ANY_RULE_SET,
GRANTEE => 'saas',
GRANT_OPTION => TRUE);
END;
/
GRANT EXECUTE ANY TYPE TO saas WITH ADMIN OPTION;
GRANT EXEMPT ACCESS POLICY TO saas WITH ADMIN OPTION;
GRANT FLASHBACK ANY TABLE TO saas WITH ADMIN OPTION;
GRANT FORCE ANY TRANSACTION TO saas WITH ADMIN OPTION;
GRANT FORCE TRANSACTION TO saas WITH ADMIN OPTION;
GRANT GLOBAL QUERY REWRITE TO saas WITH ADMIN OPTION;
GRANT GRANT ANY OBJECT PRIVILEGE TO saas WITH ADMIN OPTION;
GRANT GRANT ANY PRIVILEGE TO saas WITH ADMIN OPTION;
GRANT GRANT ANY ROLE TO saas WITH ADMIN OPTION;
GRANT INSERT ANY TABLE TO saas WITH ADMIN OPTION;
GRANT LOCK ANY TABLE TO saas WITH ADMIN OPTION;
BEGIN
SYS.DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE (
PRIVILEGE => 'MANAGE_ANY',
GRANTEE => 'saas',
ADMIN_OPTION => TRUE);
END;
/
GRANT MANAGE TABLESPACE TO saas WITH ADMIN OPTION;
GRANT ON COMMIT REFRESH TO saas WITH ADMIN OPTION;
GRANT QUERY REWRITE TO saas WITH ADMIN OPTION;
GRANT RESTRICTED SESSION TO saas WITH ADMIN OPTION;
GRANT RESUMABLE TO saas WITH ADMIN OPTION;
GRANT SELECT ANY DICTIONARY TO saas WITH ADMIN OPTION;
GRANT SELECT ANY SEQUENCE TO saas WITH ADMIN OPTION;
GRANT SELECT ANY TABLE TO saas WITH ADMIN OPTION;
GRANT UNDER ANY TABLE TO saas WITH ADMIN OPTION;
GRANT UNDER ANY TYPE TO saas WITH ADMIN OPTION;
GRANT UNDER ANY VIEW TO saas WITH ADMIN OPTION;
GRANT UNLIMITED TABLESPACE TO saas WITH ADMIN OPTION;
GRANT UPDATE ANY TABLE TO saas WITH ADMIN OPTION;
-- 1 Tablespace Quota for saas
ALTER USER saas QUOTA UNLIMITED ON BJDVDATA;
-- 3 Resoure Group Privileges for saas
BEGIN
SYS.DBMS_RESOURCE_MANAGER.clear_pending_area();
SYS.DBMS_RESOURCE_MANAGER.create_pending_area();
SYS.DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP
('saas','LOW_GROUP',TRUE);
SYS.DBMS_RESOURCE_MANAGER.submit_pending_area(); END;
/
BEGIN
SYS.DBMS_RESOURCE_MANAGER.clear_pending_area();
SYS.DBMS_RESOURCE_MANAGER.create_pending_area();
SYS.DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP
('saas','SYS_GROUP',TRUE);
SYS.DBMS_RESOURCE_MANAGER.submit_pending_area(); END;
/
BEGIN
SYS.DBMS_RESOURCE_MANAGER.clear_pending_area();
SYS.DBMS_RESOURCE_MANAGER.create_pending_area();
SYS.DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP
('saas','DEFAULT_CONSUMER_GROUP',TRUE);
SYS.DBMS_RESOURCE_MANAGER.submit_pending_area(); END;
/
BEGIN
SYS.DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP
('saas','DEFAULT_CONSUMER_GROUP');
END;
/