限制用户访问数据库另外一个用户的对象，reference how to clone user privileges [ID 473317.1]

最新推荐文章于 2022-01-24 22:37:28 发布

翁特锋

最新推荐文章于 2022-01-24 22:37:28 发布

阅读量694

点赞数

分类专栏： Oracle Scripts 文章标签：数据库 database oracle

本文链接：https://blog.csdn.net/wengtf/article/details/9036401

版权

Oracle Scripts 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

故事背景：

作为非db 开发技术人员，当有如题这种需求时候，作为运维人员需要告诉开发哪些用户目前有哪些权限即可：

1.当需要知道某个用户角色和角色的具体权限时候，如下脚本即可满足：

select lpad(' ', 2*level) || granted_role "User, his roles and privileges" 
from ( /* THE USERS */ select null grantee, username granted_role 
from dba_users 
where username like upper('%&enter_username%') 
/* THE ROLES TO ROLES RELATIONS */ 
union 
select grantee, granted_role 
from dba_role_privs 
/* THE ROLES TO PRIVILEGE RELATIONS */ 
union 
select grantee, privilege 
from dba_sys_privs ) 
start with grantee is null connect by grantee = prior granted_role

/*input the username which you want to query*/
Enter value for enter_username: scott 
old 12: username like upper('%&enter_username%') 
new 12: username like upper('%scott%'）

##Script Output

User, his roles and privileges 
-------------------------------------------------------------------------------- 
SCOTT 
CONNECT 
CREATE SESSION 
RESOURCE 
CREATE CLUSTER 
CREATE INDEXTYPE 
CREATE OPERATOR 
CREATE PROCEDURE 
CREATE SEQUENCE 
CREATE TABLE 
CREATE TRIGGER 

User, his roles and privileges 
-------------------------------------------------------------------------------- 
CREATE TYPE 
UNLIMITED TABLESPACE 

13 rows selected.

2.但当某个schema中出现新增的对象（表、视图、sequence等等）时候，我们需要一个自动的添加这种权限给你想需要赋权的用户：

CONN SCOTT/TIGER

BEGIN
  FOR x IN (SELECT table_name FROM user_tables) LOOP
    EXECUTE IMMEDIATE 'GRANT SELECT ON ' || x.table_name || ' TO HR';
  END LOOP;
END;

另：如果需要其他权限可在上述权限中增加 grant privilege 即可 <感谢彪彪提供>