Oracle用户&&权限管理

原创张均云和恩墨 2022-10-21

977

一. 概述

本片文章主要介绍了：

用户的创建，修改，删除
权限的分类
系统权限和对象权限的赋予
角色的定义与使用
回收权限

二. 用户

2.1 创建用户格式

CREATE USER user_name IDENTIFIED BY pwssword;

user_name 是用户名
password 是用户密码

【GRANT privileges TO user_name IDENTIFIED BY password】可以实现赋权与建用户同时进行;

2.2 创建用户

创建一个名为damon的用户密码为damon

SYS@oradb> create user damon identified  by damon;

User created.

2.3 修改密码

方法一：使用alter user

ALTER USER user_name identified by new_password；

方法二：psassword user

password user_name

示例：

SYS@oradb> alter user damon identified by oracle;

User altered.

SYS@oradb> PASSWORD damon
Changing password for damon
New password:
Retype new password:
Password changed

2.4 解锁用户

ALTER USER user_name IDENTIFIED BY password ACCOUNT UNLOCK;

2.5 切换用户登录

语法： conn user_name/password
使用新建用户登录：

SYS@oradb> conn damon/damon
ERROR:
ORA-01045: user DAMON lacks CREATE SESSION privilege; logon denied


Warning: You are no longer connected to ORACLE.
@> show user
USER is ""
@> conn / as sysdba
Connected.
SYS@oradb> !oerr ora 1045
01045, 00000, "user %s lacks CREATE SESSION privilege; logon denied"
// *Cause: A connect was attempted to a userid which does not have
//         create session privilege.
// *Action: Grant the user CREATE SESSION privilege.

发现链接失败，是因为该用户没有赋予创建会话的权限。处理方法后续说明。

2.6 删除用户

语法： DROP USER user_name [CASCADE];
当用户下还有其他对象的时候（表、索引、视图等），直接使用DROP USER user_name 会报错，需要加上【CASCADE】参数级联删除该用户下的所有对象。

三. 权限

3.1 权限分类

权限的作用是为了保证数据库的安全：包括系统安全和数据安全
权限可以分为系统权限和对象权限

系统权限：对于数据库的权限，能够在数据库做什么操作（例如创建表）

对象权限：操作数据库对象的权限，能够对指定的对象做什么操作（例如对表的DML操作）

schema(模式/方案）:某个用户下所有对象的集合。模式与用户是一一对应的，可以把其理解为用户。
示例：我们查看scott模式下的emp表（emp表是用户scott用户下的一个表对象）

SYS@oradb> select * from scott.emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

3.2 系统权限

3.2.1 用户的系统权限

用户创建之后，DBA可以赋予该用户一些权限
语法： GRANT privilege1[,privilege2,…] TO user1[,user1|role,PUBLIC…];

privilege一般可选：
-CREATE SESSION
-CREATE TABLE
-CREATE SEQUENCE
-CREATE VIEW
-CREATE PROCEDURE

示例：

SYS@oradb> GRANT create session,create table,create sequence,create view TO damon;

Grant succeeded.

连接damon用户,此时能正常链接，因为赋予了damon用户创建会话的

SYS@oradb> conn damon/damon
Connected.
DAMON@oradb>

查看用户拥有的系统权限

DAMON@oradb> select * from user_sys_privs;

USERNAME             PRIVILEGE                                ADM COM INH
-------------------- ---------------------------------------- --- --- ---
DAMON                CREATE VIEW                              NO  NO  NO
DAMON                CREATE SEQUENCE                          NO  NO  NO
DAMON                CREATE TABLE                             NO  NO  NO
DAMON                CREATE SESSION                           NO  NO  NO

3.3 角色

3.3.1 角色的定义

角色可以理解为一些权限的集合。角色的作用是用来简化权限的管理。例如，我们在3.2.1节中把四个系统权限都付与给了用户damon，但是如果我们需要将好几十个权限赋给damon，此时还需要将每个权限的名称（create table）全部输入，费力不讨好。此时角色就能便于我们对用户权限的管理。我们可以创建一个或多个角色，将这几十个权限一并或者分组赋予给这些创建的角色，再把这一个或多个角色赋予用户。此时该用户就拥有它所有角色下的权限。图解如下：

3.3.2 角色的使用

创建角色

CREATE ROLE role_name;

为角色赋予权限

GRANT PRIVILEGE1[,PRIVILEGE2,...] TO role_name;

将角色赋予用户

GRANT role_name TO user_name;

查看当前用户下角色信息：

SELECT * FROM USER_ROLE_PRIVS;

查看某角色拥有的系统权限

SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE='role_name';

查看某角色拥有的对象权限

SELECT * FROM ROLE_TAB_PRIVS WHERE ROLE='role_name';

3.3.3 系统默认角色

[CONNECT]、[RESOURCE]是两个系统默认的角色

查看这两个默认角色包含的系统权限

SYS@oradb> SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE IN('CONNECT','RESOURCE');

ROLE                 PRIVILEGE                                ADM COM INH
-------------------- ---------------------------------------- --- --- ---
CONNECT              SET CONTAINER                            NO  YES YES
RESOURCE             CREATE SEQUENCE                          NO  YES YES
RESOURCE             CREATE TRIGGER                           NO  YES YES
RESOURCE             CREATE CLUSTER                           NO  YES YES
RESOURCE             CREATE PROCEDURE                         NO  YES YES
RESOURCE             CREATE TYPE                              NO  YES YES
CONNECT              CREATE SESSION                           NO  YES YES
RESOURCE             CREATE OPERATOR                          NO  YES YES
RESOURCE             CREATE TABLE                             NO  YES YES
RESOURCE             CREATE INDEXTYPE                         NO  YES YES

这两个角色能给用户能够完成任务的最低标准

授予damon用户这两个角色

SYS@oradb> grant connect,resource to damon;

Grant succeeded.

SYS@oradb> conn damon/damon
Connected.
DAMON@oradb> select * from user_role_privs;

USERNAME             GRANTED_ROLE         ADM DEL DEF OS_ COM INH
-------------------- -------------------- --- --- --- --- --- ---
DAMON                CONNECT              NO  NO  YES NO  NO  NO
DAMON                RESOURCE             NO  NO  YES NO  NO  NO

3.4 对象权限

不同的对象拥有不同的对象权限

对象的拥有者拥有所有权限
比如用户创建了一个对象，该用户拥有这个对象的所有权限
对象的拥有者可以向外分配权限
示例：让damon用户能够查看scott用户拥有的表emp

# 登连接到表emp的拥有用户
DAMON@oradb> conn scott/tiger
Connected.
# 使用scott用户对用户damon赋予查询emp表的权限
SCOTT@oradb> grant select on emp to damon;

Grant succeeded.
# 查看damon用户拥有的对象权限
DAMON@oradb> select * from  user_tab_privs;

GRANTEE    OWNER      TABLE_NAME           GRANTOR              PRIVILEGE       GRA HIE COM TYPE       INH
---------- ---------- -------------------- -------------------- --------------- --- --- --- ---------- ---
DAMON      SCOTT      EMP                  SCOTT                SELECT          NO  NO  NO  TABLE      NO

# 查询scott用户下的emp表
DAMON@oradb> select count(*) from scott.emp;

  COUNT(*)
----------
        14

对象的权限可以精确到对列做insert/update：grant update (dname,loc) on dept to damon;

3.4.1 语法

对于授予的对象权限语法可以表示为：

GRANT  object_priv[(columns)]
ON     object
TO     {user|role|PUBLIC}
[WITH GRANT OPTION]

3.4.2 [WITH GRANT OPTION]

这是赋权时候的可选项，它的作用是，让被赋权的用户能够赋权给其他用户或角色；例如，A把权限授予B，B还可以把这个权限赋予给其他用户或者角色C；
示例：scott赋予damon查看emp的权限，damon赋予角色se查询emp的权限；

SCOTT@oradb> grant select on emp to damon with grant option;
Grant succeeded.

DAMON@oradb> select * from  user_tab_privs;
GRANTEE    OWNER      TABLE_NAME           GRANTOR              PRIVILEGE       GRA HIE COM TYPE       INH
---------- ---------- -------------------- -------------------- --------------- --- --- --- ---------- ---
DAMON      SCOTT      EMP                  SCOTT                SELECT          YES NO  NO  TABLE      NO

DAMON@oradb> grant select on scott.emp to se;
Grant succeeded.
DAMON@oradb> select * from role_tab_privs where role='SE';
ROLE                 OWNER      TABLE_NAME           COLUMN_NAME          PRIVILEGE       GRA COM INH
-------------------- ---------- -------------------- -------------------- --------------- --- --- ---
SE                   SCOTT      EMP                                       SELECT          NO  NO  NO

3.4.3 [PUBLIC]

PUBLIC也是一个默认的角色，数据库中任意一个角色都拥有public角色，授予给public的绝权限（grant…to public),会让所有的用户都拥有这个权限。
它的信息可以在user_tab_privs表中：

DAMON@oradb> select * FROM USER_tab_privs;

GRANTEE    OWNER      TABLE_NAME           GRANTOR              PRIVILEGE       GRA HIE COM TYPE       INH
---------- ---------- -------------------- -------------------- --------------- --- --- --- ---------- ---
DAMON      SCOTT      EMP                  SCOTT                SELECT          YES NO  NO  TABLE      NO
PUBLIC     SYS        DAMON                DAMON                INHERIT PRIVILE NO  NO  NO  USER       NO
                                                                GES

3.5 权限相关的数据字典视图

数据字典视图在前几小节也有使用到，下面是总结的表格：

3.6 收回权限

使用【REVOKE】语句撤销权限
使用WITH GRANT OPTION 子句分配的权限同样被收回

3.6.1 语法

REVOKE {privilege1[,privilege2,...]|ALL}
ON object
FROM {user1[,user2...]|role|PUBLIC}
[CASCADE CONSTRAINTS] 
{[WITH GRANT OPTION]|[WITH ADMIN OPTION]};

CASCADE CONSTRAINTS:
收回外键约束权限的时候，先将外键约束删除

WITH GRANT OPTION: 对象权限
假设权限：A -> B -> C -> D
对象权限是级联的，A收回B，那么C和D的权限一并收回
但是A不能直接收回C的权限

WITH ADMIN OPTION: 系统权限
假设权限：A -> B -> C -> D
系统权限的回收不是级联的：A回收B的权限，C和D的系统权限还存在
A可以直接回收C和D的权限，只要使用了该选项

3.7 [SELECT ANY TABLE]

SYS用户可以把[SELECT ANY TABLE]赋权给其他用户，这样这个用户就可以访问其他用户所有的表，但是该用户依旧无法查询dba_users这样的数据字典视图。
如果想让普通用户就能查到dba_数据字典视图，需要使用【SELECT ANY DICTIONARY】的权限。

四. 总结

五. 练习

5.1 使用 DBA 账户创建一个账号为 temp,密码为 temp 用户,并授予会话创建权限

可以在赋权的同时创建角色

SYS@oradb> grant create session to "temp" identified by temp;

Grant succeeded.

SYS@oradb> conn "temp"/temp
Connected.

5.2 以 SCOTT 用户登录，将查询 emp 表的权限授权给 temp 用户.

temp@oradb> conn scott/tiger
Connected.
SCOTT@oradb> grant select on emp to "temp";

Grant succeeded.

temp@oradb> select * from user_tab_privs;

GRANTEE    OWNER      TABLE_NAME           GRANTOR              PRIVILEGE       GRA HIE COM TYPE       INH
---------- ---------- -------------------- -------------------- --------------- --- --- --- ---------- ---
temp       SCOTT      EMP                  SCOTT                SELECT          NO  NO  NO  TABLE      NO
PUBLIC     SYS        temp                 temp                 INHERIT PRIVILE NO  NO  NO  USER       NO

5.3使用 temp 用户登录。

尝试查询 emp 表中部门号为 20 的所有员工信息，是否成功?

temp@oradb> SELECT * FROM SCOTT.EMP WHERE DEPTNO=20;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

尝试删除 emp 表中部门号为 20 的所有员工信息，是否成功?

temp@oradb> DELETE FROM SCOTT.EMP WHERE DEPTNO=20;
DELETE FROM SCOTT.EMP WHERE DEPTNO=20
                  *
ERROR at line 1:
ORA-01031: insufficient privileges

没有权限

5.4 以 DBA 用户，创建用户 temp2,密码为 temp2,并授予会话创建权限。

SYS@oradb> grant create session to temp2 identified by temp2;

Grant succeeded.

5.5 以 DBA 用户，创建角色 temp_role.

SYS@oradb> CREATE ROLE TEMP_ROLE;

Role created.

5.6 以 scott 用户登录，将查询 scott.dept 表的权限授权给 temp_role

SCOTT@oradb> grant select on dept to temp_role;

Grant succeeded.

5.7 将角色 temp_role 授给用户 temp 和 temp2

SYS@oradb> grant temp_role to "temp",temp2;

Grant succeeded.

5.8 用 temp2 用户登录，查询表 scott.dept 的所有信息

TEMP2@oradb> select * from scott.dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

5.9 删除角色 temp_role。重复操作第5.8

SYS@oradb> DROP ROLE TEMP_ROLE;
Role dropped.

TEMP2@oradb> select * from scott.dept;
select * from scott.dept
                    *
ERROR at line 1:
ORA-00942: table or view does not exist

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

文章被以下合辑收录

Oracle日常管理/体系学习（共5篇）

本合辑主要包含了oracle日常管理知识及本人对oracle体系的学习笔记

Oracle SQL学习笔记（共9篇）

oracle萌新的sql学习笔记