--檢視許可權

select * from dba_sys_privs where GRANTEE='CBSS_PARA_MODI';(角色對應的許可權)

select * from dba_role_privs where GRANTEE='UOP_STA1';（使用者對應的角色）

grant select,insert,delete,update on UCR_CPROD.TD_S_PROD_RES_REL_SYNC to CBSS_DATA_MODI;

--檢視一個使用者所有的許可權及角色

select privilege from dba_sys_privs where grantee='WZSB'

union

select privilege from dba_sys_privs where grantee in

(select granted_role from dba_role_privs where grantee='WZSB' );

--建立使用者並授權

create user al_heqing identified by TMYauVOi default tablespace users TEMPORARY TABLESPACE temp1 quota 50m on users;

create user al_taokangwu identified by TMYauVOi default tablespace users TEMPORARY TABLESPACE temp1 quota 50m on users;

grant CBSS_DATA_MODI to al_heqing;

grant CBSS_DATA_MODI to al_taokangwu;

grant select any table,create session to AL_HEQING;

grant select any table,create session to AL_TAOKANGWU;

--給角色賦權

grant select,insert,update,delete on UCR_CPROD.TF_M_PROD_FILE to CBSS_DATA_MODI;

grant alter any table to CBSS_DATA_MODI;

grant create any index to CBSS_DATA_MODI;

grant alter any index to CBSS_DATA_MODI;

grant create table to CBSS_DATA_MODI;

grant create view to CBSS_DATA_MODI;

grant create sequence to CBSS_DATA_MODI;

grant create SYNONYM to CBSS_DATA_MODI;

grant alter any table to CBSS_DATA_MODI;

grant drop any view to CBSS_DATA_MODI;

grant alter any sequence to CBSS_DATA_MODI;

grant drop any SYNONYM to CBSS_DATA_MODI;

grant drop any procedure to CBSS_DATA_MODI;

--賦予使用者指定表空間許可權

GRANT UNLIMITED TABLESPACE to al_huangqz;

GRANT UNLIMITED TABLESPACE to al_wangyan5;

--賦予新增表註釋許可權

grant comment any table to CBSS_DATA_MODI;

--給儲存過程賦權

grant debug,execute on uop_cen1.p_prdo_productinfo_cbss to UOP_CPROD,UOP_CEN_PROD;

grant debug,execute on UCR_CEN_PROD.P_INTF_TERMINAL to UOP_CEN_PROD;

--刪除許可權

revoke debug any procedure, debug connect session from uop_act1;

revoke execute any procedure from uop_act1;

--批次賦權

select 'grant ' || a.privilege || ' on ' || a.owner || '.' || a.table_name ||

' to ' || a.grantee || ';'

from dba_tab_privs a

where a.grantee like '%CRM1'

or a.grantor like '%CRM1'

--批次收回index許可權

select 'revoke index on '||a.TABLE_OWNER||'.'||a.TABLE_NAME||' from ' ||a.owner||';' from all_synonyms a where a.OWNER like 'U%'

###檢視鎖定的使用者

select * from v$locked_object;

select username,lock_date from dba_users; 及時間

###給使用者解鎖

alter user uif_act1_sta1 account lock;

授權指令碼

相關文章