Oracle Roles学习与测试
Oracle Roles学习与测试
参考文档
<
<
目录
1 Introduction to Roles
2 who can grant or revoke roles
3 Security Domains of Roles and Users
4 DDL statements and Roles
5 Predefined Roles
6 Revoking User privilege and roles
7 set role statement,enable role
8 specifing default roles
9 Restricting the Number of Roles that a User Can Enable
10 Notes
1 Introduction to Roles
1) Roles are named groups of related privileges that you grant to users or other roles.Roles are designed to ease the administration of end-user system and schema object privileges.However, roles are not meant to used for application developers, because the privileges to access schema objects within stored programmatic constructs need to be granted directly.(Roles主要用于方便权限管理,后半句什么意思呢?什么情况下的权限需要直接授予呢?存储过程,触发器)
2) The DBA can create a role with a password to prevent unauthorized use of the privileges granted to the role.(角色可以加密)
3) Roles are not contained in any schema. Therefore, a user who creates a role can be dropped with no effect on the role.(角色不属于任何用户schema,谁可以建角色呢? with create role 权限)
2 who can grant or revoke roles
1) Any user with the GRANT ANY ROLE system privilege can grant or revoke any role except a global role to or from other users or roles of the database.
2) Any user granted a role with the ADMIN OPTION can grant or revoke that role to or from other users or roles of the database.
3 Security Domains of Roles and Users
1) A role’s security domain includes the privileges granted to the role plus those privileges granted to any roles that are granted to the role.(角色的安全域,包括直接赋予角色的权限和通过角色赋予的权限)
2) A user’s security domain includes privileges on all schema ojbects in the corresponding schema,the privileges granted to the user, and the privileges of roles to the user that are currently enabled.also includes the privileges and roles granted to the user group PUBLIC.(用户的安全域)
4 DDL statements and Roles
Oracle avoids the dependencies on privileges received by way of roles by restricting the use of specific privileges in certain DDL statements, The following rules outline these privilege restrictions concerning DDL statements:
1) All system privileges and schema object privileges that permit a user to perform. a DDL operation are usable when received through a role.(通过角色得到的DDL权限在DDL操作中是可用的)
2) All system privileges and object privileges that allow a user to perform. a DML operation that is required to issuse a DDL statement are not usable when received through a role.(通过角色得到的DML权限在DDL操作中不可用)
Test
conn test/test
create role test_role;
grant create view to test_role;
grant select on employee to test_role;
grant test_role to hr;
grant select on depart to hr;
conn hr/hr
idle> conn hr/hr
Connected.
idle> create view employee_view as select * from test.employee;
create view employee_view as select * from test.employee
*
ERROR at line 1:
ORA-01031: insufficient privileges
idle> create view depart_view as select * from test.depart;
View created.
5 Predefined Roles
These roles are defined automatically for Oracle databases:
CONNECT: ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE VIEW
RESOURCE: CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER, CREATE TYPE
DBA: All system privileges WITH ADMIN OPTION
EXP_FULL_DATABASE: Provides the privileges required to perform. full and incremental database exports. Includes: SELECT ANY TABLE, BACKUP ANY TABLE, EXECUTE ANY PROCEDURE, EXECUTE ANY TYPE, ADMINISTER RESOURCE MANAGER, and INSERT,
DELETE, and UPDATE on the tables SYS.INCVID, SYS.INCFIL, and SYS.INCEXP. Also the following roles: EXECUTE_CATALOG_ROLE and SELECT_CATALOG_ROLE.
IMP_FULL_DATABASE: Provides the privileges required to perform. full database imports. Includes an extensive list of system privileges (use view DBA_SYS_PRIVS to view privileges) and the following roles: EXECUTE_CATALOG_ROLE and SELECT_CATALOG_ROLE.
还有很多 ….
6 Revoking User privilege and roles
6.1 revoking system privileges
Any user with the ADMIN OPTION for a system privilege or role can revoke the privilege or role from any other database user or role.The revoker does not have to be the user that originally granted the privilege or role. Users with GRANT ANY ROLE can revoke any role.(回收系统权限的人不一定必须是授权者,只要拥有ADMIN OPTION or GRANT ANY ROLE权限)
REVOKE CREATE TABLE, accts_rec FROM tsmith;
6.2 revoking object privileges
To revoke an object privilege, you must fulfill one of the following conditions:
1) you previously granted the object privilege to the user or role.(授权者)
2) you possess the GRANT ANY OBJECT PRIVILEGES system privilege that enables you to grant and revoke privileges on behalf of the object owner.
6.3 Revok column-selective object privilege
#先收再放
GRANT UPDATE (dname,deptno) ON dept TO human_resources;
REVOKE UPDATE ON dept FROM human_resources;
GRANT UPDATE (dname) ON dept TO human_resources;
只回收了deptno
7 set role statement,enable role
During the session , the user or an application can use the SET ROLE statement to enable roles for the session.of course, you must already have been granted the roles.
#enable a role
Set role clerk identified by test;
#disable all role
Set role none;
8 specifing default roles
When a user logs on, Oracle enables all privileges gratned explicitly to the user and all privileges in the user’s default roles.
User Default roles 类似 default tablespaces, can be specified in CREATE USER statement or ALTER USER statement. The user must have already been directly granted the roles with a GRANT statement
Alter user test default role clerk;
9 Restricting the Number of Roles that a User Can Enable
A user can enable as many roles as specified by the initialization parameter MAX_ENABLED_ROLES.
idle> show parameter MAX_ENABLED_ROLES
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_enabled_roles integer 30
10 Notes
1 Object privileges cannot be granted along with system privileges and roles in the same GRANT statement.
Grant select on employee, test_role to hr
*
ERROR at line 1:
ORA-00905: missing keyword
2 Roles can also be granted through a network service or operating system
3 A role can be simultaneously enabled for one user and disabled for another
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/10248702/viewspace-625178/ ，如需转载，请注明出处，否则将追究法律责任。
