授权脚本

--查看权限

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;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29337971/viewspace-1853867/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29337971/viewspace-1853867/