课程目标：
权限分类：
模式是用户下面对象的集合
1、系统权限
例子：通过 dba_sys_privs视图查看当前系统的系统权限
SQL> select count(distinct privilege) from dba_sys_privs;
COUNT(DISTINCTPRIVILEGE)
------------------------
202
SQL> select distinct privilege from dba_sys_privs order by 1;
PRIVILEGE
----------------------------------------
ADMINISTER ANY SQL TUNING SET
（1）创建用户
语法：
例子：创建一个用户名为user1，密码为user1的用户
SQL> create user user1 identified by user1;
User created.
例子：修改用户user1的默认表空间为users，配额为10M，临时表空间为temp
SQL> alter user user1 identified by user1 default tablespace users temporary tablespace temp quota 10m on users;
User altered.
（2）授予用户系统权限
创建好用户后，没有赋予权限是不能进行登录的
例子：使用刚刚创建的用户连接数据库报错，没有创建session的权限
SQL> conn user1/user1
ERROR:
ORA-01045: user USER1 lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.
授予系统权限的语法：
系统开发人员需要的权限如下：
例子：授予系统权限给用户
SQL> conn / as sysdba
Connected.
SQL> grant create session,create table,create sequence,create view to user1;
Grant succeeded.
现在登录
SQL> conn user1/user1
Connected.
通过session_privs视图查看该用户的系统权限
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE TABLE
CREATE VIEW
CREATE SEQUENCE
（3）创建和授予权限给角色
角色是一系列权限的集合，是为了简化用户权限的分配和管理，可以将角色授予给用户和其他角色。
例子：查看当前用户关于角色的权限
SQL> conn / as sysdba
Connected.
SQL> select * from session_privs where privilege like '%ROLE%';
PRIVILEGE
----------------------------------------
CREATE ROLE
DROP ANY ROLE
GRANT ANY ROLE
ALTER ANY ROLE
例子：创建角色，给角色授予权限，将角色授予用户
SQL> create role manager;
Role created.
SQL> grant create session,create table,create sequence,create view to manager;
Grant succeeded.
SQL> create user user2 identified by user2;
User created.
SQL> grant manager,create synonym to user2;
Grant succeeded.
SQL> conn user2/user2;
Connected.
SQL> select * from session_roles;
ROLE
------------------------------
MANAGER
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE TABLE
CREATE SYNONYM
CREATE VIEW
CREATE SEQUENCE
（4）修改你的密码
用户可以使用alter user语句修改自己的密码
例子：修改自己的密码
SQL> show user
USER is "USER2"
SQL> alter user user2 identified by user2;
User altered.
也可以使用password命令来修改密码
SQL> password user2
Changing password for user2
Old password:
New password:
Retype new password:
Password changed
dba可以使用password命令修改其他用户的密码
SQL> conn / as sysdba
Connected.
SQL> password user2
Changing password for user2
New password:
Retype new password:
Password changed
2、对象权限
语法：
这里一次只能针对一个对象的权限进行授权
例子：将employees表的查询权限授予user1，user2
SQL> conn hr/hr
Connected.
SQL> grant select on employees to user1,user2;
Grant succeeded.
例子：将更新departments表department_name，location_id列的权限授予user1，manager
SQL> grant update(department_name,location_id) on departments to user1,manager;
Grant succeeded.
例子：将departments表select，insert权限授予user1，且user1可以转授权
SQL> grant select,insert on departments to user1 with grant option;
Grant succeeded.
例子：将departments表的选择权限授予public，所有用户都可以访问
SQL> grant select on hr.departments to public;
Grant succeeded.
3、与权限相关的数据字典视图
例子：查看赋予角色的系统权限
SQL> conn hr/hr
Connected.
SQL> select * from role_sys_privs;
ROLE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
RESOURCE CREATE SEQUENCE NO
RESOURCE CREATE TRIGGER NO
RESOURCE CREATE CLUSTER NO
RESOURCE CREATE PROCEDURE NO
RESOURCE CREATE TYPE NO
RESOURCE CREATE OPERATOR NO
RESOURCE CREATE TABLE NO
RESOURCE CREATE INDEXTYPE NO
8 rows selected.
例子：查看授予角色的表权限
SQL> conn user2/user2;
Connected.
SQL> select * from role_tab_privs;
ROLE OWNER TABLE_NAME COLUMN_NAME PRIVILEGE GRA
---------- ---------- ------------------------------ ------------------------------ ---------- ---
MANAGER HR DEPARTMENTS LOCATION_ID UPDATE NO
MANAGER HR DEPARTMENTS DEPARTMENT_NAME UPDATE NO
MANAGER HR JOBS SELECT NO
例子：查看用户拥有的角色
SQL> select * from user_role_privs;
USERNAME GRANTED_ROLE ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
USER2 MANAGER NO YES NO
SQL> conn hr/hr
Connected.
SQL> select * from user_role_privs;
USERNAME GRANTED_ROLE ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
HR RESOURCE NO YES NO
例子：查看授予给其他用户的对象权限
SQL> select * from user_tab_privs_made;
GRANTEE TABLE_NAME GRANTOR PRIVILEGE GRA HIE
-------------------- -------------------- -------------------- -------------------- --- ---
USER1 EMPLOYEES HR SELECT NO NO
当前用户HR授予用户USER1表EMPLOYEES的SELECT权限。
例子：查看当前用户获取的其他用户授予的对象权限
SQL> select * from user_tab_privs_recd;
OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE
---------- -------------------- -------------------- -------------------- --- ---
SYS DBMS_STATS SYS EXECUTE NO NO
SQL> conn user2/user2;
Connected.
SQL> select * from user_tab_privs_recd;
OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE
---------- -------------------- -------------------- -------------------- --- ---
HR EMPLOYEES HR SELECT NO NO
例子：查询授予给其他用户的及当前用户获取的对象权限（user_tab_privs_made加上user_tab_privs_recd）
SQL> conn hr/hr
Connected.
SQL> select * from user_tab_privs;
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE
-------------------- ---------- -------------------- -------------------- -------------------- --- ---
HR SYS DBMS_STATS SYS EXECUTE NO NO
OE HR COUNTRIES HR REFERENCES NO NO
OE HR COUNTRIES HR SELECT NO NO
USER2 HR DEPARTMENTS USER1 SELECT NO NO
USER2 HR DEPARTMENTS USER1 INSERT NO NO
PUBLIC HR DEPARTMENTS HR SELECT NO NO
USER1 HR DEPARTMENTS HR SELECT YES NO
USER1 HR DEPARTMENTS HR INSERT YES NO
OE HR DEPARTMENTS HR SELECT NO NO
OE HR EMPLOYEES HR REFERENCES NO NO
OE HR EMPLOYEES HR SELECT NO NO
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE
-------------------- ---------- -------------------- -------------------- -------------------- --- ---
USER2 HR EMPLOYEES HR SELECT NO NO
USER1 HR EMPLOYEES HR SELECT NO NO
MANAGER HR JOBS HR SELECT NO NO
OE HR JOBS HR SELECT NO NO
OE HR JOB_HISTORY HR SELECT NO NO
OE HR LOCATIONS HR SELECT NO NO
OE HR LOCATIONS HR REFERENCES NO NO
18 rows selected.
例子：查看授予给其他用户的表列的对象权限
SQL> conn hr/hr
Connected.
SQL> select * from user_col_privs_made;
GRANTEE TABLE_NAME COLUMN_NAME GRANTOR PRIVILEGE GRA
-------------------- -------------------- ------------------------------ -------------------- -------------------- ---
USER1 DEPARTMENTS LOCATION_ID HR UPDATE NO
例子：查看当前用户获取的其他用户授予的表列的对象权限
SQL> conn user1/user1;
Connected.
SQL> select * from user_col_privs_recd;
OWNER TABLE_NAME COLUMN_NAME GRANTOR PRIVILEGE GRA
---------- -------------------- ------------------------------ -------------------- -------------------- ---
HR DEPARTMENTS DEPARTMENT_NAME HR UPDATE NO
HR DEPARTMENTS LOCATION_ID HR UPDATE NO
例子：查询授予给其他用户的及当前用户获取的表列的对象权限（user_col_privs_made加上user_col_privs_recd）
SQL> conn user1/user1;
Connected.
SQL> create table dept as select * from hr.departments;
Table created.
SQL> grant update(department_id) on dept to user2;
Grant succeeded.
SQL> select * from user_col_privs_made;
GRANTEE TABLE_NAME COLUMN_NAME GRANTOR PRIVILEGE GRA
-------------------- -------------------- ------------------------------ -------------------- -------------------- ---
USER2 DEPT DEPARTMENT_ID USER1 UPDATE NO
SQL> select * from user_col_privs_recd;
OWNER TABLE_NAME COLUMN_NAME GRANTOR PRIVILEGE GRA
---------- -------------------- ------------------------------ -------------------- -------------------- ---
HR DEPARTMENTS DEPARTMENT_NAME HR UPDATE NO
HR DEPARTMENTS LOCATION_ID HR UPDATE NO
SQL> select * from user_col_privs;
GRANTEE OWNER TABLE_NAME COLUMN_NAME GRANTOR PRIVILEGE GRA
-------------------- ---------- -------------------- ------------------------------ -------------------- -------------------- ---
USER1 HR DEPARTMENTS LOCATION_ID HR UPDATE NO
USER1 HR DEPARTMENTS DEPARTMENT_NAME HR UPDATE NO
USER2 USER1 DEPT DEPARTMENT_ID USER1 UPDATE NO
例子：查看授予给用户的系统权限（不包含角色里面的系统权限）
SQL> conn user2/user2
Connected.
SQL> select * from role_sys_privs;
ROLE PRIVILEGE ADM
------------------------------ -------------------- ---
MANAGER CREATE SEQUENCE NO
MANAGER CREATE SESSION NO
MANAGER CREATE TABLE NO
MANAGER CREATE VIEW NO
以上是当前用户的角色的系统权限
SQL> select * from user_sys_privs;
USERNAME PRIVILEGE ADM
------------------------------ -------------------- ---
USER2 CREATE SYNONYM NO
以上是给当前用户单独赋予的系统权限
SQL> select * from session_privs;
PRIVILEGE
--------------------
CREATE SESSION
CREATE TABLE
CREATE SYNONYM
CREATE VIEW
CREATE SEQUENCE
以上是当前用户的所有的系统权限
通过下面的示例可以看出用户的session_privs是role_sys_privs和user_sys_privs的并集
SQL> conn hr/hr
Connected.
SQL> select * from role_sys_privs;
ROLE PRIVILEGE ADM
------------------------------ -------------------- ---
RESOURCE CREATE SEQUENCE NO
RESOURCE CREATE TRIGGER NO
RESOURCE CREATE CLUSTER NO
RESOURCE CREATE PROCEDURE NO
RESOURCE CREATE TYPE NO
RESOURCE CREATE OPERATOR NO
RESOURCE CREATE TABLE NO
RESOURCE CREATE INDEXTYPE NO
8 rows selected.
SQL> select * from user_sys_privs;
USERNAME PRIVILEGE ADM
------------------------------ -------------------- ---
HR CREATE VIEW NO
HR UNLIMITED TABLESPACE NO
HR CREATE DATABASE LINK NO
HR CREATE SEQUENCE NO
HR CREATE SESSION NO
HR ALTER SESSION NO
HR CREATE SYNONYM NO
7 rows selected.
SQL> select privilege from role_sys_privs
2 union
3 select privilege from user_sys_privs;
PRIVILEGE
--------------------
ALTER SESSION
CREATE CLUSTER
CREATE DATABASE LINK
CREATE INDEXTYPE
CREATE OPERATOR
CREATE PROCEDURE
CREATE SEQUENCE
CREATE SESSION
CREATE SYNONYM
CREATE TABLE
CREATE TRIGGER
PRIVILEGE
--------------------
CREATE TYPE
CREATE VIEW
UNLIMITED TABLESPACE
14 rows selected.
SQL> select * from session_privs;
PRIVILEGE
--------------------
CREATE SESSION
ALTER SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SYNONYM
CREATE VIEW
CREATE SEQUENCE
CREATE DATABASE LINK
CREATE PROCEDURE
CREATE TRIGGER
PRIVILEGE
--------------------
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
14 rows selected.
4、回收权限
对象权限级联回收
系统权限不能级联回收
语法：
例子：用户hr回收授予给用户user1的对departments的select和insert对象权限
SQL> conn hr/hr
Connected.
SQL> select * from user_tab_privs_made where grantee='USER1' and table_name='DEPARTMENTS';
GRANTEE TABLE_NAME GRANTOR PRIVILEGE GRA HIE
-------------------- -------------------- -------------------- -------------------- --- ---
USER1 DEPARTMENTS HR INSERT YES NO
USER1 DEPARTMENTS HR SELECT YES NO
SQL> conn user1/user1
Connected.
SQL> grant select,insert on hr.departments to user2;
Grant succeeded.
SQL> conn hr/hr
Connected.
SQL> select * from user_tab_privs where table_name='DEPARTMENTS' and grantee like '%USER%';
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE
-------------------- ---------- -------------------- -------------------- -------------------- --- ---
USER1 HR DEPARTMENTS HR INSERT YES NO
USER1 HR DEPARTMENTS HR SELECT YES NO
USER2 HR DEPARTMENTS USER1 INSERT NO NO
USER2 HR DEPARTMENTS USER1 SELECT NO NO
从这儿可以看到用户HR将表DEPARTMENTS的INSERT和SELECT权限授予给USER1，然后USER1又将这两个权限授予给了USER2。
那么用户HR能否直接回收用户USER2的这两个权限呢？试一试
SQL> revoke select,insert on departments from user2;
revoke select,insert on departments from user2
*
ERROR at line 1:
ORA-01927: cannot REVOKE privileges you did not grant
不行哈，那先回收用户USER1的这两个权限
SQL> revoke select,insert on departments from user1;
Revoke succeeded.
再查看一下权限呢
SQL> select * from user_tab_privs where table_name='DEPARTMENTS' and grantee like '%USER%';
no rows selected
可以看到USER1和USER2的这两个权限都被回收了，说明对象权限可以级联回收。
我们看看系统权限可不可以级联回收：
SQL> conn / as sysdba
Connected.
SQL> create user user3 identified by user3;
User created.
SQL> create user user4 identified by user4;
User created.
SQL> grant connect to user3;
Grant succeeded.
SQL> grant connect to user4;
Grant succeeded.
SQL> conn user3/user3;
Connected.
SQL> select * from session_privs;
PRIVILEGE
--------------------
CREATE SESSION
SQL> conn / as sysdba
Connected.
SQL> grant create table to user3 with admin option;
Grant succeeded.
SQL> conn user3/user3;
Connected.
SQL> select * from user_sys_privs;
USERNAME PRIVILEGE ADM
------------------------------ -------------------- ---
USER3 CREATE TABLE YES
SQL> grant create table to user4;
Grant succeeded.
SQL> conn user4/user4;
Connected.
SQL> select * from user_sys_privs;
USERNAME PRIVILEGE ADM
------------------------------ -------------------- ---
USER4 CREATE TABLE NO
SQL> conn / as sysdba
Connected.
SQL> revoke create table from user3;
Revoke succeeded.
SQL> conn user4/user4;
Connected.
SQL> select * from user_sys_privs;
USERNAME PRIVILEGE ADM
------------------------------ -------------------- ---
USER4 CREATE TABLE NO
可以看到系统权限不能级联回收。
5、总结
6、相关习题
（1）OE and SCOTT are the users in the database. The ORDERS table is owned by OE. Evaluate the statements issued by the DBA in the following sequence: CREATE ROLE r1 GRANT SELECT, INSERT ON oe.orders TO r1 GRANT r1 TO scott GRANT SELECT ON oe.orders TO scott REVOKE SELECT ON oe.orders FROM scott What would be the outcome after executing the statements ？
A.SCOTT would be able to query the OE.ORDERS table.
B.SCOTT would not be able to query the OE.ORDERS table.
C.The REVOKE statement would remove the SELECT privilege from SCOTT as well as from the role R1.
D.The REVOKE statement would give an error because the SELECT privilege has been granted to the
role R1.
答案：A
（2）Which statement correctly grants a system privilege?
A.GRANT EXECUTE ON proc1 TO PUBLIC?
B.GRANT CREATE VIEW ON table1 TO user1?
C.GRANT CREATE TABLE TO user1,user2?
D.GRANT CREATE SESSION TO ALL?
答案：C
（3）User OE, the owner of the ORDERS table, issues the following command:
GRANT SELECT ,INSERT ON orders TO hr WITH GRANT OPTION?
The user HR issues the following command:
GRANT SELECT ON oe.orders TO scott?
Then, OE issues the following command:
REVOKE ALL ON orders FROM hr?
Which statement is correct?
A.The user SCOTT loses the privilege to select rows from OE.ORDERS.
B.The user SCOTT retains the privilege to select rows from OE.ORDERS.
C.The REVOKE statement generates an error because OE has to first revoke the SELECT privilege from SCOTT.
D.The REVOKE statement generates an error because the ALL keyword cannot be used for privileges that have been granted using WITH GRANT OPTION.
答案：A
（4）SCOTT is a user in the database. Evaluate the commands issued by the DBA: 1 CREATE ROLE mgr;2 GRANT CREATE TABLE, SELECT ON oe.orders TO mgr;3 GRANT mgr, create table TO SCOTT;Which statement is true regarding the execution of the above commands?
A.Statement 1 would not execute because the WITH GRANT option is missing.
B.Statement 1 would not execute because the IDENTIFIED BY clause is missing.
C.Statement 3 would not execute because role and system privileges cannot be granted together in a single GRANT statement.
D.Statement 2 would not execute because system privileges and object privileges cannot be granted together in a single GRANT command.
答案：D
（5）View the Exhibit and examine the structure of the ORDERS table. The ORDERS table belongs to the user OE. HR is another user in the database. Evaluate the commands issued by users OE and HR in the following order: Statement 1 by user OE: GRANT SELECT, UPDATE(customer_id, order_total) ON orders TO hr;Statement 1 by user HR: SELECT * FROM oe.orders;Statement 2 by user HR: UPDATE oe.orders SET order_total= 10000;Which statement is true regarding the above commands?
A.Statement 1 by user OE would not work because the statement has to be issued by the DBA.
B.Statement 2 by user HR would not work because the grant is only for SELECT in a subquery of update.
C.There are no errors in the statements issued by OE and HR? all the statements would execute successfully.
D.Statement 1 by user HR would not work because SELECT and UPDATE privileges have been granted only on CUSTOMER_ID and ORDER_TOTAL columns.
答案：C
（6）The user SCOTT who is the owner of ORDERS and ORDER_ITEMS tables issues the following GRANT command: GRANT ALL ON orders, order_items TO PUBLIC；What correction needs to be done to the above statement ?
A.PUBLIC should be replaced with specific usernames.
B.ALL should be replaced with a list of specific privileges.
C.WITH GRANT OPTION should be added to the statement.
D.Separate GRANT statements are required for ORDERS and ORDER_ITEMS tables.
答案：D
（7）Which two statements are true regarding roles (Choose two.)
A.A role can be granted to itself.
B.A role can be granted to PUBLIC.
C.A user can be granted only one role at any point of time.
D.The REVOKE command can be used to remove privileges but not roles from other users.
E.Roles are named groups of related privileges that can be granted to users or other roles.
答案：BE
（8）Which statement correctly differentiates a system privilege from an object privilege ？
A.System privileges can be granted only by the DBA whereas object privileges can be granted by DBAs or the owner of the object.
B.System privileges give the rights to only create user schemas whereas object privileges give rights to manipulate objects in a schema.
C.Users require system privileges to gain access to the database whereas they require object privileges to create objects in the database.
D.A system privilege is the right to perform specific activities in a database whereas an object privilege is a right to perform activities on a specific object in the database.
答案：D
（9）Which statement is true regarding the SESSION_PRIVS dictionary view ?
A.It contains the current object privileges available in the user session.
B.It contains the current system privileges available in the user session.
C.It contains the object privileges granted to other users by the current user session.
D.It contains the system privileges granted to other users by the current user session.
答案：B
| 欢迎光临 重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/) | Powered by Discuz! X3.2 |
