用户权限

mac2022-06-30 116

环境搭建

SQL> create user dev identified by oracle;

User created.

SQL> grant create session to dev;

Grant succeeded.

SQL> show user;

USER is "DEV"

一．授予用户create session 的权限

1.1 检查用户的系统权限

SQL> desc user_sys_privs;

Name Null? Type

----------------------------------------- -------- ----------------------------

USERNAME VARCHAR2(30)

PRIVILEGE NOT NULL VARCHAR2(40)

ADMIN_OPTION VARCHAR2(3)

SQL> select username,privilege from user_sys_privs;

USERNAME PRIVILEGE

------------------------------ ----------------------------------------

DEV CREATE SESSION

Dev只有创建会话的权限

1.2 检查用户的对象权限

SQL> desc user_tab_privs;

Name Null? Type

----------------------------------------- -------- ----------------------------

GRANTEE NOT NULL VARCHAR2(30)

OWNER NOT NULL VARCHAR2(30)

TABLE_NAME NOT NULL VARCHAR2(30)

GRANTOR NOT NULL VARCHAR2(30)

PRIVILEGE NOT NULL VARCHAR2(40)

GRANTABLE VARCHAR2(3)

HIERARCHY VARCHAR2(3)

SQL> select table_name,privilege from user_tab_privs;

no rows selected

1.3 检查用户的角色权限

SQL> desc user_role_privs;

Name Null? Type

----------------------------------------- -------- ----------------------------

USERNAME VARCHAR2(30)

GRANTED_ROLE VARCHAR2(30)

ADMIN_OPTION VARCHAR2(3)

DEFAULT_ROLE VARCHAR2(3)

OS_GRANTED VARCHAR2(3)

SQL> select username,default_role from user_role_privs;

no rows selected

1.4 检查角色的系统权限

SQL> desc role_sys_privs;

Name Null? Type

----------------------------------------- -------- ----------------------------

ROLE NOT NULL VARCHAR2(30)

PRIVILEGE NOT NULL VARCHAR2(40)

ADMIN_OPTION VARCHAR2(3)

SQL> select role,privilege from role_sys_privs;

no rows selected

1.5 检查角色的对象权限

SQL> desc role_tab_privs;

Name Null? Type

----------------------------------------- -------- ----------------------------

ROLE NOT NULL VARCHAR2(30)

OWNER NOT NULL VARCHAR2(30)

TABLE_NAME NOT NULL VARCHAR2(30)

COLUMN_NAME VARCHAR2(30)

PRIVILEGE NOT NULL VARCHAR2(40)

GRANTABLE VARCHAR2(3)

SQL> select table_name,column_name,privilege from role_tab_privs;

no rows selected

二．授予用户select any table 的权限

SQL> grant select any table to dev;

Grant succeeded.

官方文档关于 select any table 的描述：

当把这个权限授予用户之后，用户可以查询任何用户的表、视图、物化视图（权限太大）

2.1 检查用户的系统权限

SQL> desc user_sys_privs;

Name Null? Type

----------------------------------------- -------- ----------------------------

USERNAME VARCHAR2(30)

PRIVILEGE NOT NULL VARCHAR2(40)

ADMIN_OPTION VARCHAR2(3)

SQL> select username,privilege from user_sys_privs;

USERNAME PRIVILEGE

------------------------------ ----------------------------------------

DEV CREATE SESSION

DEV SELECT ANY TABLE

Dev已经有select any table 的权限

2.2 检查用户的对象权限

SQL> desc user_tab_privs;

Name Null? Type

----------------------------------------- -------- ----------------------------

GRANTEE NOT NULL VARCHAR2(30)

OWNER NOT NULL VARCHAR2(30)

TABLE_NAME NOT NULL VARCHAR2(30)

GRANTOR NOT NULL VARCHAR2(30)

PRIVILEGE NOT NULL VARCHAR2(40)

GRANTABLE VARCHAR2(3)

HIERARCHY VARCHAR2(3)

SQL> select table_name,privilege from user_tab_privs where table_name='EMP';

no rows selected

2.3 检查用户的角色权限

SQL> desc user_role_privs;

Name Null? Type

----------------------------------------- -------- ----------------------------

USERNAME VARCHAR2(30)

GRANTED_ROLE VARCHAR2(30)

ADMIN_OPTION VARCHAR2(3)

DEFAULT_ROLE VARCHAR2(3)

OS_GRANTED VARCHAR2(3)

SQL> select username,default_role from user_role_privs;

no rows selected

2.4 检查角色的系统权限

SQL> desc role_sys_privs;

Name Null? Type

----------------------------------------- -------- ----------------------------

ROLE NOT NULL VARCHAR2(30)

PRIVILEGE NOT NULL VARCHAR2(40)

ADMIN_OPTION VARCHAR2(3)

SQL> select role,privilege from role_sys_privs;

no rows selected

2.5 检查角色的对象权限

SQL> desc role_tab_privs;

Name Null? Type

----------------------------------------- -------- ----------------------------

ROLE NOT NULL VARCHAR2(30)

OWNER NOT NULL VARCHAR2(30)

TABLE_NAME NOT NULL VARCHAR2(30)

COLUMN_NAME VARCHAR2(30)

PRIVILEGE NOT NULL VARCHAR2(40)

GRANTABLE VARCHAR2(3)

SQL> select table_name,column_name,privilege from role_tab_privs;

no rows selected

2.6 测试

更新

SQL> update scott.emp set empno=empno*1;

update scott.emp set empno=empno*1

ERROR at line 1:

ORA-01031: insufficient privileges

SQL> rollback;

Rollback complete.

删除

SQL> delete from scott.emp ;

delete from scott.emp

ERROR at line 1:

ORA-01031: insufficient privileges

SQL> rollback;

Rollback complete.

插入

SQL> insert into scott.emp(empno) values(123);

insert into scott.emp(empno) values(123)

ERROR at line 1:

ORA-01031: insufficient privileges

SQL> rollback;

Rollback complete.

三．授予用户对其他用户下单个对象只读的权限

SQL> grant all on scott.emp to dev;

Grant succeeded.

SQL> revoke delete on scott.emp from dev;

Revoke succeeded.

SQL> revoke update on scott.emp from dev;

Revoke succeeded.

3.1 检查用户的系统权限

SQL> desc user_sys_privs;

Name Null? Type

----------------------------------------- -------- ----------------------------

USERNAME VARCHAR2(30)

PRIVILEGE NOT NULL VARCHAR2(40)

ADMIN_OPTION VARCHAR2(3)

Rollback complete.

SQL> select username,privilege from user_sys_privs;

USERNAME PRIVILEGE

------------------------------ ----------------------------------------

DEV CREATE SESSION

Dev已经有CREATE SESSION 的权限

3.2 检查用户的对象权限

SQL> desc user_tab_privs;

Name Null? Type

----------------------------------------- -------- ----------------------------

GRANTEE NOT NULL VARCHAR2(30)

OWNER NOT NULL VARCHAR2(30)

TABLE_NAME NOT NULL VARCHAR2(30)

GRANTOR NOT NULL VARCHAR2(30)

PRIVILEGE NOT NULL VARCHAR2(40)

GRANTABLE VARCHAR2(3)

HIERARCHY VARCHAR2(3)

SQL> select table_name,privilege from user_tab_privs;

TABLE_NAME PRIVILEGE

------------------------------ ----------------------------------------

EMP FLASHBACK

EMP DEBUG

EMP QUERY REWRITE

EMP ON COMMIT REFRESH

EMP REFERENCES

EMP SELECT

EMP INSERT

EMP INDEX

EMP ALTER

3.3 检查用户的角色权限

SQL> desc user_role_privs;

Name Null? Type

----------------------------------------- -------- ----------------------------

USERNAME VARCHAR2(30)

GRANTED_ROLE VARCHAR2(30)

ADMIN_OPTION VARCHAR2(3)

DEFAULT_ROLE VARCHAR2(3)

OS_GRANTED VARCHAR2(3)

SQL> select username,default_role from user_role_privs;

USERNAME DEF

------------------------------ ---

DEV YES

3.4 检查角色的系统权限

SQL> desc role_sys_privs;

Name Null? Type

----------------------------------------- -------- ----------------------------

ROLE NOT NULL VARCHAR2(30)

PRIVILEGE NOT NULL VARCHAR2(40)

ADMIN_OPTION VARCHAR2(3)

SQL> select role,privilege from role_sys_privs;

no rows selected

3.5 检查角色的对象权限

SQL> desc role_tab_privs;

Name Null? Type

----------------------------------------- -------- ----------------------------

ROLE NOT NULL VARCHAR2(30)

OWNER NOT NULL VARCHAR2(30)

TABLE_NAME NOT NULL VARCHAR2(30)

COLUMN_NAME VARCHAR2(30)

PRIVILEGE NOT NULL VARCHAR2(40)

GRANTABLE VARCHAR2(3)

SQL> select table_name,column_name,privilege from role_tab_privs;

no rows selected

3.6 测试

更新

SQL> update scott.emp set empno=empno*1;

update scott.emp set empno=empno*1

ERROR at line 1:

ORA-01031: insufficient privileges

SQL> rollback;

Rollback complete.

删除

SQL> delete from scott.emp ;

delete from scott.emp

ERROR at line 1:

ORA-01031: insufficient privileges

SQL> rollback;

Rollback complete.

插入

SQL> insert into scott.emp(empno) values(123);

1 row created.

SQL> commit;

Rollback complete.

转载于:https://www.cnblogs.com/iyoume2008/p/5157719.html

最新回复(0)

用户权限

一．授予用户create session 的权限

1.1 检查用户的系统权限

1.2 检查用户的对象权限

1.3 检查用户的角色权限

1.4 检查角色的系统权限

1.5 检查角色的对象权限

二．授予用户select any table 的权限

2.1 检查用户的系统权限

2.2 检查用户的对象权限

2.3 检查用户的角色权限

2.4 检查角色的系统权限

2.5 检查角色的对象权限

2.6 测试

三．授予用户对其他用户下 单个对象 只读的权限

3.1 检查用户的系统权限

3.2 检查用户的对象权限

3.3 检查用户的角色权限

3.4 检查角色的系统权限

3.5 检查角色的对象权限

3.6 测试

三．授予用户对其他用户下单个对象只读的权限