|
课程目标： - 区分系统权限和对象权限
- 赋予表权限
- 通过数据字典查询权限
- 赋予角色
- 区分权限和角色
权限分类： - 系统权限，是指对数据库进行访问的权限，比如创建连接，表，数据库，表空间等
- 对象权限，是指对数据库对象内容进行操作的权限，比如增加记录，删除记录等
模式是用户下面对象的集合 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、对象权限 - 不同的对象有不同的对象权限
- 用户有其用户模式下所有对象的对象权限
- 对象权限可以转授权给其他用户或者角色
- 系统权限和对象权限不能在一条grant语句里面
语法：
这里一次只能针对一个对象的权限进行授权 例子：将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认证学习论坛
( 渝ICP备12004239号-4 )
发表于 2015-12-14 12:50:22
QQ好友和群
QQ空间
腾讯微博
腾讯朋友
收藏
支持
反对