Oracle Database Security-object permissions

Q:PUBLIC is granted the following permissions WITH the 'with GRANT option' OPTION:

 

 

 

 

GRANTEE	OWNER	TABLE_NAME	GRANTOR	PRIVILEGE	GRANTABLE
PUBLIC	SYS	PLAN_TABLE $	SYS	UPDATE	YES
PUBLIC	SYS	PLAN_TABLE $	SYS	INSERT	YES
PUBLIC	SYS	PLAN_TABLE $	SYS	DELETE	YES
PUBLIC	SYS	OLAPTABLEVELS	SYS	UPDATE	YES
PUBLIC	SYS	OLAPTABLEVELS	SYS	INSERT	YES
PUBLIC	SYS	OLAPTABLEVELS	SYS	DELETE	YES
PUBLIC	SYS	OLAPTABLEVELTUPLES	SYS	UPDATE	YES
PUBLIC	SYS	OLAPTABLEVELTUPLES	SYS	INSERT	YES
PUBLIC	SYS	OLAPTABLEVELTUPLES	SYS	DELETE	YES

 

 

The following description is mentioned in the Oracle Security Guide:

"NOTE: The 'Grant option' OPTION is invalid when the object permission is granted to the role. The Oracle Database prohibits the dissemination of object permissions through roles. Therefore, the role's grantee cannot disseminate the object permissions obtained through the role ."

If PUBLIC is recognized as a role, the following conclusions can be drawn:

1. In the above circumstances, the 'Grant option' is invalid?

2. PUBLIC (PUBLIC users of the system) cannot disseminate the object permissions they have obtained?

A:To answer the question, we will use an experiment to verify the document.

First, we create two users: USER_ABBIE and USER_BOB. In addition, we will create a role: USER_BOB_ROLE. Then, we use the USER_ABBIE user to create the TEST_TABLE table and GRANT the SELECT permission on the TEST_TABLE to the USER_BOB_ROLE role with the 'Grant OPTION 'OPTION.

The experiment is performed in Oracle 10 Gb database. The SQL script used in the experiment can be used in your Oracle database environment.

First, create users and roles and assign basic permissions. NOTE: For the production environment, I do not recommend assigning permissions in this way. The best practice is to use the minimum permission principle.

 

 

SQL> create user USER_ABBIE identified by "For #12 Testing # 12Only ";   2 default tablespace USERS 3 temporary tablespace TEMP; User created. SQL> grant CONNECT, RESOURCE to USER_ABBIE; Grant succeeded. SQL> create user USER_BOB identified by "For #345 Testing # 345Only "; 2 default tablespace USERS 3 temporary tablespace TEMP; User created. SQL> grant CONNECT, RESOURCE to USER_BOB; Grant succeeded. SQL> create user USER_CHLOE identified by "For #789 Testing # 789Only "; 2 default tablespace USERS 3 temporary tablespace TEMP; User created. SQL> grant CONNECT, RESOURCE to USER_CHLOE; Grant succeeded. SQL> create role USER_BOB_ROLE; Role created.

 

 

Now, we will create a table for the USER_ABBIE user and GRANT the SELECT permission to the USER_BOB_ROLE role with the 'Grant option' OPTION.

 

 

SQL> conn USER_ABBIE/"For #12 Testing # 12Only"   Connected. SQL> create table TEST_TABLE 2 as select * from DUAL; Table created. SQL> column DUMMY format a10; SQL> select * from TEST_TABLE; DUMMY ---- X SQL> grant SELECT on TEST_TABLE to USER_BOB_ROLE with GRANT OPTION; Grant SELECT on TEST_TABLE to USER_BOB_ROLE with GRANT OPTION * ERROR at line 1: ORA-01926: cannot GRANT to a role WITH GRANT OPTION

 

 

 

As you can see, Oracle Database throws a ORA-01926 error to prevent this situation!

 

From a security perspective, it is very dangerous to use the GRANT and ADMIN options if you do not fully understand how object access permissions are propagated and how complex they are. For example, when you revoke the object access permission that was originally assigned using the 'Grant option' OPTION, you can detach the cascade action to all permissions held by the grantee. When the 'admin option' OPTION is used, when the authorizer's own permissions are revoked, the granted permissions will still be granted to all the authorized users. When many users are maintained, this can easily lead to unexpected permission granting.

Best practices:Assign permissions without the GRANT/ADMIN option to the role and then assign these roles to the specified user.

Author: perfect