Oracle Roles學習與測試

參考文件

<>P631

<>P749

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

Oracle Roles學習與測試

相關文章