320 likes | 534 Views
SECTION 5. CONTROL DATABASE PRIVILEGES 控制数据库权限 SECTION 5 OBJECTIVES 本章学习目的 AT THE END OF THIS SECTION, YOU SHOULD BE ABLE TO: 本章结束,您将能够: DEFINE PRIVILEGES 定义权限 GRANT AND CONTROL SYSTEM PRIVILEGES 授予和控制系统权限 GRANT AND CONTROL OBJECT PRIVILEGES 授予和控制对象权限 CREATE AND CONTROL ROLES 设定和控制角色
E N D
SECTION 5 • CONTROL DATABASE PRIVILEGES控制数据库权限 • SECTION 5 OBJECTIVES本章学习目的 • AT THE END OF THIS SECTION, YOU SHOULD BE ABLE TO:本章结束,您将能够: • DEFINE PRIVILEGES定义权限 • GRANT AND CONTROL SYSTEM PRIVILEGES授予和控制系统权限 • GRANT AND CONTROL OBJECT PRIVILEGES授予和控制对象权限 • CREATE AND CONTROL ROLES设定和控制角色 • CONTROL DATABASE PRIVILEGES控制数据库权限
OVERVIEW概述 • GRANT USERS PRIVILEGES TO ACCESS THE DATABASE AND OBJECTS WITHIN THE DATABASE, AND TO ALLOW THEM SPECIFIC SYSTEM PRIVILEGES • 授予用户访问数据库以及数据库对象权限,同时允许他们有特殊的系统权限。 • * ENABLE AND RESTRICT ACCESS AND CHANGES TO DATA • 启用并限制访问数据和改变数据。 • * PROVIDE A USER THE RIGHT TO PERFORM A TYPE OF OPERATION • 允许用户进行键盘操作。 • * ENABLE AND RESTRICT THE ABILITY TO PERFORM SYSTEM FUNCTIONS AND CHANGE DATABASE STRUCTURES • 启用并限制执行系统功能和改变数据库结构的性能。 • * GRANT TO INDIVIDUAL USERS AND ROLES承认独立的用户和角色。 • * GRANT TO ALL USERS(PUBLIC)承认所有的用户(公众) • PRIVILEGE TYPE特权类型
DESCRIPTION具体描述 • ----->SYSTEM系统 • A PRIVILEGE OR A RIGHT TO PERFORM A PARTICULAR ACTION, OR TO PERFORM A PARTICULAR ACTION ON A PARTICULAR TYPE OF OBJECT完成特殊操作的权限,以及在指定对象类型上执行特定操作。 • OBJECT对象 • A PRIVILEGE OR RIGHT TO PERFORM A PARTICULAR ACITON ON A SPECIFIC TABLE, VIEW, SEQUENCE, PROCEDURE, FUNCTION, OR PACKAGE对象权限指用户在指定的表、视图、序列数产生器、过程、函数或包上执行特殊操作的权利。 • CONTROL DATABASE PRIVILEGES控制数据库权限 • OVERVIEW (CONTINUED)概述(续) • MAINTAIN AND CONTROL PRIVILEGES BY CREATING ROLES THAT CONSIST OF A NAMED GROUP OF RELATED PRIVILEGES.通过建立含有相关权限的已命名组维持或控制权限。
ROLE PROPERTIES任务工具 • * REDUCED GRANTING OF PRIVILEGESS简化权限授予 • * DYNAMIC PRIVILEGE MANAGEMENT动态权限的管理 • * SELECTIVE AVAILABILITY OF PRIVILEGES有选择的权限有效性 • * APPLICATION AWARENESS应用程序认知
SYSTEM PRIVILEGES系统权限 • ALLOW USERS TO PERFORM A PARTICULAR DATABASE OPERATION OR CLASS OF OPERATIONS BY GRANTING THEM SYSTEM PRIVILEGES. • 通过授予系统权限,允许用户执行特殊的数据库操作及操作类型。 • A SYSTEM PRIVILEGE IS A RIGHT TO EXECUTE A TYPE OF COMMAND. • 系统权限是执行某种命令类型的权利。 • TYPE OF SYSTEM PRIVILEGES: • 系统权限类型: • * IN ONE扴 OWN SCHEMA在个人模块上 • THE PRIVILEGE TO CREATE A TABLE IN ONE扴 OWN SCHEMA • 在个人模块上设置表的权限。 • THE PRIVILEGE TO CREATE A SEQUENCE IN ONE扴 OWN SCHEMA • 在个人模块上设置序列的权限。
* ON ALL OBJECTS OF A SPECIFIED TYPE在指定类型的所有对象上 • THE PRIVILEGE TO CREATE A TABLE IN ANY SCHEMA在任何模块上创建新表的权限 • THE PRIVILEGE TO UPDATE ROWS IN ANY TABLE OR VIEW IN ANY SCHEMA • 在任何模块更新行和在任何模块上更新视图的权限。 • * ON THE SYSTEM OR A USER在系统或用户上 • THE PRIVILEGE TO CREATE A USER建立新用户的权限 • THE PRIVILEGE TO CREATE A SESSION (CONNECT TO THE DATABASE)建立新的会话的权限(与数据库相连)
SYSTEM PRIVILEGE NOTES系统权限注意条则 • * SYSTEM PRIVILEGES ARE NOT SPECIFIC TO A NAMED SCHEMA OBJECT OR STRUCTURE. THEY ARE SPECIFIC TO A PARTICULAR OPERATION OR CLASS OF OPERATIONS ON A TYPE OF OBJECT OR STRUCTURE. • 对已命名的模式对象或构造,系统权限并非是明确的。而对于对象或构造类型的特殊操作,则系统特权是明确的。 • * FOR EXAMPLE, THE SYSTEM PRIVILEGE SELECT ANY TABLE GIVES THE USER THE RIGHT TO QUERY ANY TABLE IN THE DATABASE. AN OBJECT PRIVILEGE GIVES A USER THE RIGHT TO QUERY A SPECIFIC TABLE.举例来说,系统特权授权给用户查询数据库中的任何表。而对象特权则授权用户查询一个具体的表。
SYSTEM PRIVILEGES (CONTINUED)系统权限(续) • THERE ARE OVER 60 DISTINCT SYSTEM PRIVILEGES. EACH SYSTEM PRIVILEGE ALLOWS A USER TO PERFORM A PARTICULAR DATABASE OPERATION OR CLASS OF DATABASE OPERATIONS. • ORACLE提供了超过60种的系统权限。其中每个系统权限允许用户执行一个或一类数据库操作。 • SYSTEM • PRIVILEGE系统权限 • COMMAND命令 • OPERATIONS PERMITTED • 允许的操作 • SESSION对话 • CREATE SESSION建立对话 • ALLOWS GRANTEE TO CONNECT TO THE DATABASE允许被授权者连接到数据库
TABLE表 • ----->CREATE TABLE建立表 • ALLOWS A GRANTEE TO CREATE TABLE IN OWN SCHEMA, ALSO ALLOWS GRANTEE TO CREATE INDEXES ON TABLES IN OWN SCHEMA, NOTE THAT THE GRANTEE MUST HAVE THE PRIVILEGE TO ALLOCATE BLOCKS IN THE TABLESPACE CONTAINING THE TABLE ALSO CONNECT, DML, DROP, ALTER, TRUNCATE.允许被授权者在自己的模式中建立表,也允许在表上建立索引。(注意该被授权者必须有在含表的表空间内分派块的权限。同时应有连接、数据操纵、删除、更改和截断的权限。) • TABLE表 • SELECT ANY TABLE选取表 • ALLOWS GRANTEE TO QUERY ANY TABLE, VIEW OR SNAPSHOT IN ANY SCHEMA • 允许被授权者在任何模式中查询任何表、视图和快照。 • GRANT SYSTEM PRIVILEGES系统权限的授予 • GRANT AND REVOKE SYSTEM PRIVILEGES TO AND FROM USERS AND ROLES USING THE GRANT SQL COMMAND. • 使用GRANT SQL命令把系统权限授予用户或角色,或从用户中回收系统权限。
SYNTAX句法 • WITH ADMIN OPTION. GRANT SYSTEM_PRIV OR ROLE TO USER OR ROLE OR PUBLIC • 使用ADMIN选项,把用户权限或角色授予给角色、用户或公众。 • WHERE: • SYSTEM_PRIV • IS A SYSTEM PRIVILEGE TO BE GRANTED被授权的系统权限 • ROLE • IS A ROLE NAME TO BE GRANTED被授权的角色名 • TO • IDENTIFIES USERS OR ROLES TO WHICH SYSTEM PRIVILEGES AND ROLES ARE GRANTED标识用户或角色为何种被授权的系统特权。 • PUBLIC • GRANTS SYSTEM PRIVILEGES OR ROLES TO ALL USERS把系统权限或角色授予所有用户 。 • WITH ADMIN • OPTION • ALLOWS THE GRANTEE TO GRANT THE SYSTEM PRIVILEGE OR ROLE TO OTHER USERS OR ROLES. IF YOU GRANT A ROLE WITH ADMIN OPTION, THE GRANTEE CAN ALSO ALTER OR DROP THE ROLE. 被授权者能够进一步把系统权限或角色授予给其他用户和角色。假如你用ADMIN OPTION授权一个角色,那么被授予者可以改变或删除该角色。
NOTES:注意事项: • 1. TO GRANT A SYSTEM PRIVILEGE, YOU MUST HAVE BEEN GRANTED THE SYSTEM PRIVILEGE WITH ADMIN OPTION • 要授予系统权限,授予者必须已使用选项被授予系统权限, • 2. A ROLE IS A NAMED GROUP OF RELATED PRIVILEGES THAT ARE GRANTED TO USERS OR OTHER ROLES. 角色是命名的有关权限和角色的组合,可把它授予用户和其他角色。 • GRANT SYSTEM PRIVILEGES系统权限的授予
EXAMPLES:例: • GRANT THE PRIVILEGE TO CONNECT TO THE DATABASE AND TO CREATE A TABLE IN MARION扴 SCHEMA TO MARION USING THE GRANT SQL COMMAND. • 使用GRANT SQL命令授予MARION与数据库相连的权限,同时授予在MARION模式上建立表的权限。 • SQLDBA> GRANT CREATE SESSION, CREATE TABLE TO MARION; • STATEMENT PROCESSED. • GRANT THE PRIVILEGE TO ALTER ANY TABLE IN ANY SCHEMA TO MARION. • 授予MARION在任何模式上更改表的权限。 • SQLDBA> GRANT ALTER ANY TABLE TO MARION; • STATEMENT PROCESSED.
GRANT WITH ADMIN OPTION授权ADMIN OPTION • GRANT USERS A SYSTEM PRIVILEGE WITH ADMIN OPTION TO ALLOW THEM TO GRANT AND REVOKE THAT PRIVILEGE TO AND FROM OTHER USERS. • 把系统权限ADMIN OPTION授予给用户,允许把该权限授予给其他用户或从中回收。 • WITH ADMIN OPTION • * THE GRANTEE CAN GRANT OR REVOKE THE SYSTEM PRIVILEGE TO OR FROM ANY OTHER USER IN THE DATABASE. • 被授权者能够授予数据库中任何其他用户系统权限或从中回收该权限。 • * THE GRANTEE CAN FURTHER GRANT THE SYSTEM PRIVILEGE WITH ADMIN OPTION. • 被授权者能够进一步授予系统权限ADMIN OPTION • * GRANTS MADE WITH ADMIN OPTION ARE NOT HIERARCHICAL. REVOKING A GRANT ... WITH ADMIN OPTION DOES NOT CASCADE. • ADMIN OPTION授予不是层次的,回收一个GRANT...WITH ADMIN OPTION也不是连锁的。 • * PRIVILEGES WITH ADMIN OPTION CANNOT BE GRANTED TO ROLES. ADMIN OPTION系统权限不能被授予角色。
GRANT WITH ADMIN OPTION系统权限ADMIN OPTION授予 • ----->EXAMPLE:例: • GRANT MARION THE PRIVILEGES TO CREATE, MANAGE AND DROP USERS WITH THE ABILITY TO PASS PRIVILEGES ON TO OTHER USERS. • 请授予MARION建立、管理和删除用户并使其具有将这些权限传给其他用户的能力。 • SQLDBA> GRANT CREATE USER, ALTER USER, DROP USER TO • 2> WITH ADMIN OPTION; • STATEMENT PROCESSED.
DISPLAY SYSTEM PRIVILEGES系统权限的显示 • LIST THE SYSTEM PRIVILEGES THAT AHVE BEEN GRANTED BY QUERYING THE VIEW DBA_SYS_PRIVS. 通过查询DBA_SYS_PRIVS视图列出已授予的系统权限。 • EXAMPLE:例: • LIST ALL SYSTEM PRIVILEGES GRANTED TO ROLES AND USERS BY DISPLAYING THE INFORMATION IN SYS.DBA_SYS_PRIVS. • 通过在SYS>DBA_SYS_PRIVS上显示信息,列出被授予给角色和用户的所有系统权限。 • SQLDBA> SELECT * • 2> FROM SYS.DBA_SYS_PRIVS • 3> ORDER BY GRANTEE, PRIVILEGE
REVOKE SYSTEM PRIVILEGES系统权限的回收 • ALTERNATIVELY, REVOKE SYSTEM PRIVILEGES FROM A USER USING THE REVOKE SQL COMMAND.使用REVOKE SQL命令有选择的从用户中回收系统权限。 • SYNTAX句法 • REVOKE SYSTEM_PRIV OR ROLE FROM USER OR ROLE OR PUBLIC • WHERE: • SYSTEM_PRIV • IS A SYSTEM PRIVILEGE TO BE REVOKED被回收的系统权限 • ROLE • IS A ROLE TO BE REVOKED被回收的角色 • FROM • IDENTIFIES USERS AND ROLES FROM WHICH THE SYSTEM PRIVILEGES ARE REVOKED. 标识用户和角色为何种被回收的系统权限。 • PUBLIC • REVOKES THE SYSTEM PRIVILEGE OR ROLE FROM ALL USERS.从所有用户中收回系统权限和角色。
REVOKE SYSTEM PRIVILEGES系统权限ADMIN OPTION的回收 • * YOU MUST HAVE BEEN GRANTED THE SYSTEM PRIVILEGE WITH ADMIN OPTION IN ORDER TO REVOKE THE WITH ADMIN OPTION PRIVILEGE. • 为回收ADMIN OPTION权限,你必须已被授予系统权限ADMIN OPTION。 • * A SYSTEM PRIVILEGE CAN BE REVOKED BY A USER OTHER THAN THE GRANTOR. • 系统权限能被授予者回收。 • * PRIVILEGES THAT ARE GRANTED OR REVOKED ARE EFFECTIVE IMMEDIATELY.被授予或回收的系统权限立即生效。 • REVOKE SYSTEM PRIVILEGES WITH ADMIN OPTION系统权限ADMIN OPTION的回收 • SPECIFYING WITH ADMIN OPTION IS NOT NECESSARY WHEN REVOKING A PRIVILEGE FROM A USER. IF THE OPTION WAS INCLUDED IN THE GRANT, IT WILL BE INCLUDED IN THE REVOKE. 当从用户手中回收系统权限ADMIN OPTION时, 不必指定权限选项。如果在授权是已包含了该选项,则回收时也就自动包含了该权限选项。 • EXAMPLE:例: • REVOKE THE PRIVILEGES TO ALTER AND DROP A USER FROM UTCKG.
回收UTCKG更改和删除用户 的系统权限。 • SQLDBA> REVOKE ALTER USER, DROP USER FROM UTCKG; • STATEMENT PROCESSED. • E语句 • SQL STATEMENTS PERMITTED BY THE OBJECT PRIVILEGES允许的SQL • OBJECT PRIVILEGE对象权限 • SQL STATEMENTS PERMITTED允许的语句 • SELECT • SELECT...FROM OBJECT (TABLE, VIEW, OR SNAPSHOT) • SQL STATEMENTS USING A SEQUENCE SELECT...FROM对象(表、视图或快照)及用序列数产生器的语句。 • UPDATE
回收UTCKG更改和删除用户 的系统权限。 • UPDATE OBJECT (TABLE OR VIEW)UPDATE对象(表或视图) • INSERT • INSERT INTO OBJECT (TABLE AND VIEW)INSERT INTO对象(表或视图) • ALTER • ALTER OBJECT (TABLE OR SEQUENCE) ALTER 对象(表或序列数产生器) • CREATE TRIGGER ON OBJECT (TABLES ONLY) CREATE TRIGGER ON对象(只在表上) • DELETE • DELETE FROM OBJECT (TABLE OR VIEW)DELETE FROM对象(表或视图) • TRUNCATE OBJECT (TABLES ONLY)对象(表)
continue..... • EXECUTE • EXECUTE OBJECT (PROCEDURE OR FUNCTION)EXECUTE对象(过程或函数) • REFERENCES TO PUBLIC PACKAGE VARIABLES引用公共包变量 • INDEX • CREATE INDEX ON OBJECT (TABLES ONLY)CREAT INDEX ON对象(只能在表上建立) • REFERENCES • CREATE OR ALTER TABLE STATEMENT DEFINING A FOREIGN KEY INTEGRITY CONSTRAINT ON OBJECT (TABLES ONLY)CREAT或ALTER TABLE语句(定义表上FOREIGN KEY的完整性制约。)
GRANT OBJECT PRIVILEGES对象权限的授予 • USE THE GRANT COMMAND TO GRANT OBJECT PRIVILEGES TO USERS AND ROLES • 使用命令把对象权限授予给用户和角色。 • SYNTAX句法 • WHERE: • OBJECT_PRIV • IS AN OBJECT PRIVILEGE TO BE GRANTED • COLUMN • SPECIFIES A TABLE OR VIEW ON WHICH PRIVILEGES ARE GRANTED. ONLY SPECIFY COLUMNS WHEN GRANTING INSERT, REFERENCES OR UPDATE PRIVILEGES. IF COLUMNS ARE NOT LISTED, THE GRANTEE HAS PRIVILEGES ON ALL COLUMNS IN THE TABLE OR VIEW. • ON
continue... • IDENTIFIES THE OBJECT ON WHICH THE PRIVILEGES ARE GRANTED. IF THE OBJECT WITH SCHEMA DOES NOT QUALIFY, ORACLE ASUMES THE OBJECT IS IN THAT SCHEMA. • TO • IDENTIFIES USERS OR ROLES TO WHICH THE OBJECT PRIVILEGE IS GRANTED. • PUBLIC • GRANTS OBJECT PRIVILEGES TO ALL USERS. • WITH GRANT OPTION • ALLOWS THE GRANTEE TO GRANT THE OBJECT PRIVILEGES TO OTHER USERS AND ROLES. THE GRANTEE MUST BE A USER OR PUBLIC. GRANT OPTION CANNOT BE GRANTED TO A ROLE.
GRANT OBJECT PRIVILEGES对象权限的授予 • EXAMPLES:例; • GRANT USERS MARION AND SAM THE PRIVILEGE TO QUERY THE ACCOUNTS TABLE. • 把对ACCOUNTS表的查询权限授予用户MARION和SAM • SQLDB> GRANT SELECT ON ACCOUNTS TO MARION, SAM; • STATEMENT PROCESSED. • GRANT USER BOB THE PRIVILEGES NECESSARY TO QUERY THE EMP TABLE, INSERT A ROW IN THE EMP TABLE WITH VALUES IN THE EMPNO, ENAME AND DEPTNO COLUMNS, AND UPDATE THE ENAME COLUMN IN THE EMP TABLE. • 把对EMPE表上的查询权限,对EMP表中的EMPNO|ENAME|DEPTNO三个属性列值的插入权限以及更新EMP表中ENAME列的对象权限授予BOB. • SQLDB> GRANT SELECT, INSERT (EMPNO, ENAME, DEPTNO), UPDATE (ENAME) • 2> ON EMP TO BOB; • STATEMENT PROCESSED. • GRANT OBJECT PRIVILEGES对象权限的授予
NOTES注意事项 • * TO GRANT PRIVILEGES ON AN OBJECT, THE OBJECT MUST BE IN YOUR OWN SCHEMA OR YOU MUST HAVE BEEN GRANTED THE OBJECT PRIVILEGES WITH GRANT OPTION. • 授予对象权限,该对象必须存在于你自己的模式中,或者,你已使用WITH GRANT OPTION被授予了对象权限 • * AN OBJECT OWNER CAN GRANT ANY OBJECT PRIVILEGE ON THE OBJECT TO ANY OTHER USER OR ROLE OF THE DATABASE. • 对象的拥有者能够把该对象上的对象权限授予其他用户或数据库角色。 • * IF THE GRANT INCLUDES THE WITH GRANT OPTION, THE GRANTEE CAN FURTHER GRANT THE OBJECT PRIVILEGES TO OTHERUSERS. • 如果一个GRANT语句中包含GRANT OPTION选项,则被授权者能进一步把该对象权限授予其他用户。 • * THE OWNER OF AN OBJECT AUTOMATICALLY ACQUIRES ALL OBJECT PRIVILEGES ON THAT OBJECT. 对象的拥有者能自动的获得该对象上的所有对象权限。 • GRANT WITH GRANT OPTION 使用WITH GRANT OPTION选项授权
notes continue.... • A PRIVILEGE THAT IS GRANTED WITH GRANT OPTION CAN BE PASSED ON TO OTHER USERS AND ROLES BY THE GRANTEE. 如果使用GRANT OPTION选项授权,则被授权者能进一步把该对象权限授予其他用户和角色。 • WITH GRANT OPTION • * IF THE GRANT INCLUDES THE WITH GRANT OPTION, THE GRANTEE CAN FURTHER GRANT THE OBJECT PRIVILEGES TO OTHER USERS. • 如果一个GRANT语句中包含GRANT OPTION选项,则被授权者能够进一步把该对象权限授予其他用户。 • * OBJECT PRIVILEGES GRANTED WITH GRANT OPTION ARE REVOKED WHEN THE GRANTOR扴 PRIVILEGE IS REVOKED. • 如果授权是使用了WITH GRANT OPTION选项,则在回收某用户的对象权限时,同时回收该用户授予其他用户的权限。 • * A PRIVILEGE WITH GRANT OPTION CAN BE GRANTED TO ROLES GRANT OPTION权限能授予给角色。
DISPLAY OBJECT PRIVILEGES对象权限的显示 • VIEW ALL OBJECT PRIVILEGES GRANTED TO USERS BY QUERYING THE DATA DICTIONARY. • 可以通过查询数据字典来了解所有对象权限授予情况。 • OBJECT PRIVILEGE VIEWS • AVAILABLE TO DBA扴 • DESCRIPTION • DBA_TAB_PRIVS • 数据库中对象上的权限描述 • ALL PRIVILEGES ON OBJECTS IN THE DATABASE • DBA_COL_PRIVS
数据库中列上的权限描述 • ----->ALL PRIVILEGES ON COLUMNS IN THE DATABASE • DISPLAY OBJECT PRIVILEGES列出所有的对象权限 • OBJECT PRIVILEGE VIEWS • AVAILABLE TO THE USER • DESCRIPTION • USER_TAB_PRIVS • PRIVILEGES ON OBJECTS FOR WHICH THE USER IS THE OWNER, GRANTOR, OR GRANTEE • USER_TAB_PRIVS_MADE
数据库中列上的权限描述continue.. • ALL PRIVILEGES ON OBJECTS OWNED BY THE USER • USER_TAB_PRIVS_RECD • PRIVILEGES ON COLUMNS FOR WHICH THE USER IS THE OWNER, GRANTOR, OR GRANTEE • USER_COL_PRIVS • PRIVILEGES ON COLUMNS FOR WHICH THE USER IS THE OWNER, GRANTOR, OR GRANTEE • USER_COL_PRIVS_MADE • ALL PRIVILEGES ON COLUMNS OF OBJECTS OWNED BY THE USER • USER_COL_PRIVS_RECD • PRIVILEGES ON COLUMNS FOR WHICH THE USER IS THE GRANTEE
ALL_TAB_PRIVS • PRIVILEGES ON OBJECTS FOR WHICH THE USER OR PUBLIC IS THE GRANTEE • ALL_TAB_PRIVS_RECD • PRIVILEGES ON OBJECTS FOR WHICH THE USER OR PUBLIC IS THE GRANTEE • TABLE_PRIVILEGES • PRIVILEGES ON OBJECTS FOR WHICH THE USER IS THE GRANTOR, GRANTEE, OR OWNER, OR PUBLIC IS THE GRANTEE • ALL_COL_PRIVS • PRIVILEGES ON COLUMNS FOR WHICH THE USER ON PUBLIC IS THE GRANTEE • ALL_COL_PRIVS_MADE • PRIVILEGES ON COLUMNS FOR WHICH THE USER IS OWNER OR GRANTOR • ALL_COL_PRIVS_RECD • PRIVILEGES ON COLUMNS FOR WHICH THE USER OR PUBLIC IS THE GRANTEE • COLUMN_PRIVILEGES • PRIVILEGES ON COLUMNS FOR WHICH THE USER IS THE GRANTOR, GRANTEE, OR OWNER, OR PUBLIC IS THE GRANTEE • REVOKE OBJECT PRIVILEGES
SYNTAX • REVOKE OBJECT PRIVILEGES WHERE: • OBJECT_PRIV • IS AN OBJECT PRIVILEGE TO BE REVOKED. • ON • IDENTIFIES THE OBJECT ON WHICH THE OBJECT PRIVILEGES ARE REVOKED • FROM • IDENTIFIES USERS AND ROLES FROM WHICH THE OBJECT PRIVILEGES ARE REVOKED • PUBLIC • REVOKES OBJECT PRIVILEGES FROM ALL USERS • CASCADE • CONSTRAINTS • DROPS ANY REFERENTIAL INTEGRITY CONSTRAINTS DEFINED USING REFERENCES PRIVILEGE THAT IS BEING REVOKED. THE OPTION TO REVOKE REFERENCES PRIVILEGES MUST BE SPECIFIED BECAUSE THE REVOKEE HAS DEFINED A REFERENTIAL INTEGRITY CONSTRAINT.
NOTE: GRANTORS CAN REVOKE PRIVILEGES FROM ONLY THOSE USRS TO WHOM THEY HAVE GRANTED PRIVILEGES. • ROLES角色 • SIMPLIFY PRIVILEGE MANAGEMENT THROUGH THE USE OF ROLES. ROLES ARE NAMED GROUPS OF RELATED PRIVILEGES THAT ARE GRANTED TO USERS OR OTHER ROLES. • 通过角色机制简化权限管理。角色是命名的相关权限和角色的组合。 • ROLES:角色: • * CAN CONSIST OF BOTH SYSTEM AND OBJECT PRIVILEGES • 角色可由系统权限和对象权限组合而成。 • * ARE NOT OWNED BY ANYONE; NOT IN ANY SCHEMA • 角色不为任何人拥有,也不属于任何一个模式。 • * MAY BE GRANTED TO ANY USER OR ROLE, EXCEPT TO ITSELF (EVEN INDIRECTLY) • 可把它授予任何用户或角色,除它本身之外(甚至非直接)。 • * CAN BE ENABLED OR DISABLED FOR EACH AUTHORIZED USER • 按用户需要处于活动或关闭状态。 • * MAY REQUIRE AUTHORIZATION (PASSWORDS) TO ENABLE可使用口令使其处于活动状态。
BENEFITS OF ROLES建立角色益处 • REDUCED GRANTING OF PRIVILEGES简化权限授予 • * GRANT OR REVOKE MANY PRIVILEGES WITH ONE STATEMENT • 用一个语句授予或回收多种权限 • * CAN GRANT A ROLE TO NEW USERS, NOT REQUIRED TO REMEMBER INDIVIDUAL PRIVILEGES NEEDED • 可把角色授给新的用户,不需记忆专用的权限。 • * SIMPLIFIES PRIVILEGE MANAGEMENT IN SYSTEMS WITH MANY USERS, MANY TABLES, OR BOTH • 简化带有大量用户、表格或两种兼有的大型系统的权限管理。