Systems Engineering and RDBMS

Back to the basics: grants on the objects

Posted by decipherinfosys on May 7, 2009

As you know, roles and privileges are used to control the user access. Based on the specific privileges, we can control whether user can perform specific database operation, select and manipulate the data, access objects and so on. Privileges are of three different types.

•    Roles: Roles are group or collection of privileges defined together. By granting role, one can grant underlying privilege to the user.
•    System Privileges: Assignment of this privilege determines whether user can perform any specific database operation or not.
•    Object Privileges: This privilege is used to control the access of objects.

In this blog post, we will see how can we check the object privileges on objects be it a table or any other objects like procedure, function etc. We are connected to scott schema and granting execute privilege to inter schema for specific procedure and function.

SQL>  connect scott/tiger@orcl

SQL> grant execute on test_func to inter;

Grant succeeded.

SQL> grant execute on test_proc to inter;

Grant succeeded.

SQL> grant select,insert,update,delete on test to inter;

Grant succeeded.

From the user who was granted the privileges, we can get object privileges information from user_tab_privs or user_tab_privs_recd view. If we are viewing it from the user who granted the privilege then we can view it from user_tab_privs_made view. Let us see it using query. First we will connect to ‘scott’ user, which granted the privilege to ‘inter’ user.

SQL> select grantor,table_name,privilege,grantee
2  from user_tab_privs_made
3    where table_name in ('TEST','TEST_PROC','TEST_FUNC');

GRANTOR    TABLE_NAME   PRIVILEGE    GRANTEE
———- ———— ———— ———-
SCOTT      TEST         DELETE       INTER
SCOTT      TEST         INSERT       INTER
SCOTT      TEST         SELECT       INTER
SCOTT      TEST         UPDATE       INTER
SCOTT      TEST_FUNC    EXECUTE      INTER
SCOTT      TEST_PROC    EXECUTE      INTER

If we query the user_tab_privs_recd view, we will not get anything since user ‘scott’ didn’t received any privileges on any objects from other user.

SQL> select owner,grantor,table_name,privilege
2  from user_tab_privs_recd
3  where table_name in ('TEST','TEST_PROC','TEST_FUNC');

no rows selected

Now let us connect to ‘inter’ schema and execute the same query again. Query which refers user_tab_privs_made view should not return any rows since it didn’t grant any privileges to any other user but query which refers user_tab_privs_recd view should return some data since ‘inter’ user received some privileges to access object of ‘scott’ user.

SQL> conn inter/inter@orcl
Connected.

SQL>
SQL> select grantor,table_name,privilege,grantee
2    from user_tab_privs_made
3   where table_name in ('TEST','TEST_PROC','TEST_FUNC');

no rows selected

Now let us issue another query.

SQL> select owner,grantor,table_name,privilege
2    from user_tab_privs_recd
3   where table_name in ('TEST','TEST_PROC','TEST_FUNC');

OWNER                          GRANTOR    TABLE_NAME   PRIVILEGE
—————————— ———- ———— ———
SCOTT                          SCOTT      TEST         DELETE
SCOTT                          SCOTT      TEST         INSERT
SCOTT                          SCOTT      TEST         SELECT
SCOTT                          SCOTT      TEST         UPDATE
SCOTT                          SCOTT      TEST_FUNC    EXECUTE
SCOTT                          SCOTT      TEST_PROC    EXECUTE

Above result indicates that owner of the object is ‘scott’ and grantor is also ‘scott’. Grantor can be different if owner has give grantable permission ‘with admin’ to the user so that he can grant privilege to other user subsequently.

View names are little misleading in this case as name indicates that it is only for tables (user_tab_privs, user_tab_privs_recd) but in fact it contains detail about the privileges of all the objects. We can also query the all_ views to get the same results.

Resources:

  • Oracle 11g Administrator guide – here.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: