ITPub博客

首页 > 数据库 > Oracle > 授权脚本

授权脚本

原创 Oracle 作者:jelephant 时间:2015-12-05 10:54:12 0 删除 编辑
--查看权限
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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-12-07

  • 博文量
    143
  • 访问量
    626819