Oracle database experiences

Oracle database blog

Database link, privileges and definer’s rights PL/SQL units

We will see how using database links, you can use the V$ views in yours definer’s rights PL/SQL units (procedures, functions) without having to grant directly to a user such views.

Let’s create the user “test_user1”:

SA> CREATE USER test_user1 IDENTIFIED BY user1;

User created.

Let’s grant the following privileges to test_user1:

SA> GRANT CREATE SESSION TO test_user1;

Grant succeeded.

SA> GRANT CREATE PROCEDURE TO test_user1;

Grant succeeded.

SA> GRANT SELECT_CATALOG_ROLE TO test_user1;

Grant succeeded.

This user can now query the v$session view:

> connect test_user1
Enter password:
Connected.
TEST_USER1> SELECT COUNT(*) FROM v$session;

  COUNT(*)
----------
        28

1 row selected.

This user can also use this view in an anonymous block:

TEST_USER1> SET SERVEROUTPUT ON
TEST_USER1> DECLARE
  2     v_count NUMBER DEFAULT 0;
  3  BEGIN
  4     SELECT COUNT(*)
  5     INTO v_count
  6     FROM v$session;
  7
  8     DBMS_OUTPUT.PUT_LINE('Count:' || TO_CHAR(v_count));
  9  END;
 10  /
Count:28

PL/SQL procedure successfully completed.

Suppose now that the user test_user1 wants to use different V$ views in a function named work_with_views:

> connect test_user1
Enter password:
Connected.
TEST_USER1> CREATE OR REPLACE FUNCTION work_with_views
  2  RETURN NUMBER
  3  AS
  4     v_count NUMBER DEFAULT 0;
  5  BEGIN
  6     SELECT COUNT(*)
  7     INTO v_count
  8     FROM v$session;
  9
 10     RETURN v_count;
 11  END;
 12  /

Warning: Function created with compilation errors.

TEST_USER1> show errors
Errors for FUNCTION WORK_WITH_VIEWS:

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/4      PL/SQL: SQL Statement ignored
8/9      PL/SQL: ORA-00942: table or view does not exist

This error means that we have to grant directly the SELECT privilege for the view v$session to the user test_user1. Let’s try this:

> connect / as sysdba
Connected.
SYS> GRANT SELECT ON v$session TO test_user1;
GRANT SELECT ON v$session TO test_user1
                *
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

Well, it does not succeeded because we have to grant the underlying object:

SYS> GRANT SELECT ON v_$session TO test_user1;

Grant succeeded.

Let’s recompile the function work_with_views:

> connect test_user1
Enter password:
Connected.
TEST_USER1> ALTER FUNCTION work_with_views COMPILE;

Function altered.

TEST_USER1> SELECT work_with_views FROM DUAL;

WORK_WITH_VIEWS
---------------
             29

1 row selected.

Now it’s working. But we can do it another way. Let’s grant the following system privilege to test_user1:

SYS> GRANT CREATE DATABASE LINK TO test_user1;

Grant succeeded.

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

TEST_USER1> CREATE DATABASE LINK "DB_LOCAL" CONNECT TO test_user1 IDENTIFIED by user1 USING 'xe';

Database link created.

Let’s revoke the following privileges from test_user1:

SYS> REVOKE CREATE DATABASE LINK FROM test_user1;

Revoke succeeded.

SYS> REVOKE SELECT ON v_$session FROM test_user1;

Revoke succeeded.

Now we will recompile the function work_with_views:

TEST_USER1> ALTER FUNCTION work_with_views COMPILE;

Warning: Function altered with compilation errors.

TEST_USER1> show errors;
Errors for FUNCTION WORK_WITH_VIEWS:

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/4      PL/SQL: SQL Statement ignored
8/9      PL/SQL: ORA-00942: table or view does not exist

We will modify the function work_with_views in order to use the private loopback database link as follow:

TEST_USER1> CREATE OR REPLACE FUNCTION work_with_views
  2  RETURN NUMBER
  3  AS
  4     v_count NUMBER DEFAULT 0;
  5  BEGIN
  6     SELECT COUNT(*)
  7     INTO v_count
  8     FROM v$session@db_local;
  9
 10     RETURN v_count;
 11  END;
 12  /

Function created.

No error and it works:

TEST_USER1> SELECT work_with_views FROM DUAL;

WORK_WITH_VIEWS
---------------
             30

1 row selected.

TEST_USER1> SELECT COUNT(*) FROM v$session;

  COUNT(*)
----------
        30

1 row selected.

You can also change the V$ view:

TEST_USER1> CREATE OR REPLACE FUNCTION work_with_views
  2  RETURN NUMBER
  3  AS
  4     v_count NUMBER DEFAULT 0;
  5  BEGIN
  6     SELECT COUNT(*)
  7     INTO v_count
  8     FROM v$sysmetric_history@db_local;
  9
 10     RETURN v_count;
 11  END;
 12  /

Function created.

TEST_USER1> SELECT work_with_views FROM DUAL;

WORK_WITH_VIEWS
---------------
           4087

1 row selected.

Remark:

  • You can also use private database link matching other databases.
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: