Q

Object privileges in Oracle database security

A reader asks a question about the GRANT OPTION as it relates to object privileges in Oracle database security.

PUBLIC has been granted the following privileges with the WITH GRANT 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 Oracle Security Guide mentions as follows:

"Note: The GRANT OPTION is not valid when granting an object privilege to a role. Oracle Database prevents the propagation of object privileges through roles so that grantees of a role cannot propagate object privileges received by means of roles."

If PUBLIC is considered a role, can I conclude that:

  1. the GRANT OPTION in the above scenario is not valid?
  2. PUBLIC (any user of the system) cannot propagate the object privileges that he has been given?

To answer your question, we’ll use a practical experiment to validate the documentation.

What we’ll do is create two users,  USER_ABBIE and USER_BOB.  We’ll also create a role, just for USER_BOB called USER_BOB_ROLE.  Then, we will have USER_ABBIE create TEST_TABLE, and grant SELECT on it to USER_BOB_ROLE with the GRANT OPTION. 

I’ll be performing this in Oracle 10g, but I’ll include all the SQL in case you’d like to try it on whatever flavor of Oracle you may be using.

First create the users and the role, and assign basic rights – NOTE: For production, I don’t recommend assigning privileges in this manner – Using the Principle of Least Privilege is the best practice.


SQL> create user USER_ABBIE identified by "For#12Testing#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#345Testing#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#789Testing#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’ll create a table as USER_ABBIE, and give the USER_BOB_ROLE the SELECT privilege on it with the GRANT OPTION.


SQL> conn USER_ABBIE/"For#12Testing#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 throws an ORA-01926, preventing this from happening! 

From a security standpoint, using the GRANT and ADMIN options for object access and privileges, respectively, can be very dangerous if you don’t fully understand the intricacies of how they propagate.  For example, revoking object access originally allotted with the GRANT OPTION will cascade through all the users that the grantee has granted access to.  Using the ADMIN OPTION, when the grantor has their privileges revoked, all grantees maintain their granted access.  When dealing with many users, it is easy for unintended privilege assignment to occur.

Best practice:  Assign privileges directly to roles, without GRANT/ADMIN options, and apply those roles to the desired users.

This was first published in June 2011

Dig deeper on Oracle database security

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close