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.
Oracle database experiences 