Sunday, October 4, 2009

Granting Object Privileges on Behalf of the Object Owner

The GRANT ANY OBJECT PRIVILEGE system privilege allows users to grant and revoke any object privilege on behalf of the object owner. This provides a convenient means for database and application administrators to grant access to objects in any schema without requiring that they connect to the schema. This eliminates the need to maintain login credentials for schema owners so that they can grant access to objects, and it reduces the number of connections required during configuration.



This system privilege is part of the Oracle supplied DBA role and is thus granted (with the ADMIN OPTION) to any user connecting AS SYSDBA (user SYS). As with other system privileges, the GRANT ANY OBJECT PRIVILEGE system privilege can only be granted by a user who possesses the ADMIN OPTION.

When you exercise the GRANT ANY OBJECT PRIVILEGE system privilege to grant an object privilege to a user, if you already possess the object privilege with the GRANT OPTION, then the grant is performed in the usual way. In this case, you become the grantor of the grant. If you do not possess the object privilege, then the object owner is shown as the grantor, even though you, with the GRANT ANY OBJECTPRIVILEGE system privilege, actually performed the grant.

By default, the SYS, SYSDBA and DBA role have this system privilege with the ADMIN option, allowing them to grant this privilege to other users.

Example
Execute the following command as user with dba rights in sqlplus tool

SELECT GRANTEE, OWNER, GRANTOR, PRIVILEGE, GRANTABLE
FROM DBA_TAB_PRIVS
WHERE TABLE_NAME = 'EMPLOYEES' and OWNER = 'HR';




GRANTEE OWNER GRANTOR PRIVILEGE GRANTABLE
--------------- ----------  --------------- ---------------- -------------------
BLAKE       HR         HR              SELECT       YES


Now assume that blake also has the GRANT ANY OBJECT PRIVILEGE system. issues the following statement:

GRANT SELECT ON hr.employees TO clark;

SELECT GRANTEE, OWNER, GRANTOR, PRIVILEGE, GRANTABLE
FROM DBA_TAB_PRIVS
WHERE TABLE_NAME = 'EMPLOYEES' and OWNER = 'HR';

GRANTEE OWNER GRANTOR PRIVILEGE GRANTABLE
--------------- ----------- --------------- ---------------- -------------------
BLAKE       HR         HR              SELECT      YES
CLARK      HR         BLAKE       SELECT       YES