Oracle database experiences

Oracle database blog

Database link, privileges and views

We will see how using database links, you can grant the SELECT privilege on views to others users without having the GRANT OPTION privilege on the base views.

Let’s create 3 users “test_user1”, “test_user2” and “test_user3”:

SA> CREATE USER test_user1 IDENTIFIED BY user1
  2  DEFAULT TABLESPACE USERS
  3  QUOTA 5M ON USERS;

User created.

SA> CREATE USER test_user2 IDENTIFIED BY user2
  2  DEFAULT TABLESPACE USERS
  3  QUOTA 5M ON USERS;

User created.

SA> CREATE USER test_user3 IDENTIFIED BY user3
  2  DEFAULT TABLESPACE USERS
  3  QUOTA 5M ON USERS;

User created.

Let’s grant the following privileges to test_user1, test_user2 and test_user3:

SA> GRANT CREATE SESSION TO test_user1;

Grant succeeded.

SA> GRANT CREATE TABLE TO test_user1;

Grant succeeded.

SA> GRANT CREATE VIEW TO test_user1;

Grant succeeded.

SA>
SA> GRANT CREATE SESSION TO test_user2;

Grant succeeded.

SA> GRANT CREATE TABLE TO test_user2;

Grant succeeded.

SA> GRANT CREATE VIEW TO test_user2;

Grant succeeded.

SA>
SA> GRANT CREATE SESSION TO test_user3;

Grant succeeded.

SA> GRANT CREATE TABLE TO test_user3;

Grant succeeded.

SA> GRANT CREATE VIEW TO test_user3;

Grant succeeded.

The user test_user1 can now create a table named table_test1 and insert 3 rows:

> connect TEST_USER1
Enter password:
Connected.
TEST_USER1> CREATE TABLE table_test1(
  2     column1 NUMBER);

Table created.

TEST_USER1>
TEST_USER1> INSERT INTO table_test1(column1) VALUES(1);

1 row created.

TEST_USER1> INSERT INTO table_test1(column1) VALUES(2);

1 row created.

TEST_USER1> INSERT INTO table_test1(column1) VALUES(3);

1 row created.

TEST_USER1> COMMIT;

Commit complete.

TEST_USER1> SELECT * FROM table_test1;

   COLUMN1
----------
         1
         2
         3

3 rows selected.

Let’s create a view named v_table_test1 based on table table_test1:

TEST_USER1> CREATE OR REPLACE VIEW v_table_test1
  2  AS
  3  SELECT column1
  4  FROM table_test1;

View created.

Let’s grant the SELECT privilege on the view v_table_test1 to user test_user2:

TEST_USER1> GRANT SELECT ON v_table_test1 TO test_user2;

Grant succeeded.

User test_user2 can now query the view v_table_test1 as follow:

> connect test_user2
Enter password:
Connected.
TEST_USER2> SELECT * FROM test_user1.v_table_test1;

   COLUMN1
----------
         1
         2
         3

3 rows selected.

User test_user2 can create a view based on the v_table_test1 view:

TEST_USER2> CREATE OR REPLACE VIEW v_test2
  2  AS
  3  SELECT column1+1 AS column1a
  4  FROM test_user1.v_table_test1;

View created.

TEST_USER2> SELECT * FROM v_test2;

  COLUMN1A
----------
         2
         3
         4

3 rows selected.

User test_user2 would like to grant the SELECT privilege on the view v_test2 to user test_user3:

TEST_USER2> GRANT SELECT ON v_test2 TO test_user3;
GRANT SELECT ON v_test2 TO test_user3
                *
ERROR at line 1:
ORA-01720: grant option does not exist for 'TEST_USER1.V_TABLE_TEST1'

It does not succeed because test_user2 must have the permission from test_user1 to propagate the base privilege on his view. Let’s give this privilege from test_user1 session:

> connect test_user1
Enter password:
Connected.
TEST_USER1> GRANT SELECT ON v_table_test1 TO test_user2 WITH GRANT OPTION;

Grant succeeded.

Let’s grant again the SELECT privilege on the view v_test2 to user test_user3:

> connect test_user2
Enter password:
Connected.
TEST_USER2> GRANT SELECT ON v_test2 TO test_user3;

Grant succeeded.

This time it is working. Let’s query the view v_test2 from test_user3 session:

> connect test_user3
Enter password:
Connected.
TEST_USER3> SELECT * FROM test_user2.v_test2;

  COLUMN1A
----------
         2
         3
         4

3 rows selected.

It works. Let’s revoke the SELECT privilege on the v_table_test1 view from test_user2:

TEST_USER1> REVOKE SELECT ON v_table_test1 FROM test_user2;

Revoke succeeded.

Let’s grant the SELECT privilege on the view v_table_test1 to user test_user2 without “WITH GRANT OPTION”:

TEST_USER1> GRANT SELECT ON v_table_test1 TO test_user2;

Grant succeeded.

Let’s revoke the SELECT privilege on the v_test2 view from test_user3:

> connect TEST_USER2
Enter password:
Connected.
TEST_USER2> REVOKE SELECT ON v_test2 FROM test_user3;

Revoke succeeded.

Let’s grant again the SELECT privilege on the view v_test2 to user test_user3:

TEST_USER2> GRANT SELECT ON v_test2 TO test_user3;
GRANT SELECT ON v_test2 TO test_user3
                *
ERROR at line 1:
ORA-01720: grant option does not exist for 'TEST_USER1.V_TABLE_TEST1'

Logically it does not work. However we could grant the SELECT privilege on the view v_test2 in modifying slightly v_test2. Let’s grant the following system privilege to test_user2:

> connect / as sysdba
Connected.
SYS> GRANT CREATE DATABASE LINK TO test_user2;

Grant succeeded.

User test_user2 can now create private database links. Let’s create a private loopback database link on our database instance:

TEST_USER2> CREATE DATABASE LINK "DB_LOCAL" CONNECT TO test_user2 IDENTIFIED by user2 USING 'xe';

Database link created.

Let’s revoke the following system privilege from test_user2:

SYS> REVOKE CREATE DATABASE LINK FROM test_user2;

Revoke succeeded.

Let’s modify the view v_test2 in order to use the private loopback database link that we have created as follow:

> connect test_user2
Enter password:
Connected.
TEST_USER2> CREATE OR REPLACE VIEW v_test2
  2  AS
  3  SELECT column1+1 AS column1a
  4  FROM test_user1.v_table_test1@db_local;

View created.

TEST_USER2> SELECT * FROM v_test2;

  COLUMN1A
----------
         2
         3
         4

3 rows selected.

User test_user2 can now grant the SELECT privilege on the view v_test2 to user test_user3:

TEST_USER2> GRANT SELECT ON v_test2 TO test_user3;

Grant succeeded.

Let’s try to select the view v_test2 from test_user3 session:

> connect test_user3
Enter password:
Connected.
TEST_USER3> SELECT * FROM test_user2.v_test2;

  COLUMN1A
----------
         2
         3
         4

3 rows selected.

It works.

Once a user have a SELECT privilege without “WITH GRANT OPTION” on a view from another user he can transmit this privilege to another user in accessing the base view through a private loopback database link.

Advertisements

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: