Ameer Khan

Automating Object Privileges in Oracle with Stored Procedures

Introduction

Granting object privileges in Oracle can be a repetitive and time-consuming task, especially when dealing with multiple objects within a schema. This blog post demonstrates how to automate granting SELECT, INSERT, UPDATE, and DELETE privileges on all tables and views of a schema to a specified user using PL/SQL stored procedures.

Creating Users

SQL> CREATE USER test IDENTIFIED BY test;

Assigning Initial Grants

We grant the necessary roles and privileges to these users to allow them to connect to the database and perform specific operations:

SQL>grant connect, resource to test;

Creating Stored Procedures for Granting Privileges

Granting SELECT on All Tables

SQL> CREATE OR REPLACE PROCEDURE grant_select(     username VARCHAR2,     grantee VARCHAR2) AS   BEGIN     FOR r IN (         SELECT owner, object_name         FROM dba_objects         WHERE owner = UPPER(username) AND OBJECT_TYPE = ‘TABLE’     )     LOOP         EXECUTE IMMEDIATE ‘GRANT SELECT ON ‘||r.owner||’.’||r.object_name||’ TO ‘ || grantee;     END LOOP; END; /

Granting SELECT on All Valid Views

CREATE OR REPLACE PROCEDURE grant_select_view(     p_username VARCHAR2,     p_grantee  VARCHAR2 ) AS   BEGIN     FOR r IN (         SELECT owner, object_name         FROM dba_objects         WHERE owner = UPPER(p_username)           AND object_type = ‘VIEW’           AND status = ‘VALID’  — Skip invalid views     )     LOOP         BEGIN             EXECUTE IMMEDIATE ‘GRANT SELECT ON ‘ || r.owner || ‘.’ || r.object_name || ‘ TO ‘ || p_grantee;         EXCEPTION             WHEN OTHERS THEN                 DBMS_OUTPUT.PUT_LINE(‘Skipping view: ‘ || r.owner || ‘.’ || r.object_name || ‘ due to error: ‘ || SQLERRM);         END;     END LOOP; END; /

Granting INSERT on All Tables

SQL>CREATE OR REPLACE PROCEDURE grant_insert(     username VARCHAR2,     grantee VARCHAR2) AS   BEGIN     FOR r IN (         SELECT owner, object_name         FROM dba_objects         WHERE owner = UPPER(username) AND OBJECT_TYPE = ‘TABLE’     )     LOOP         EXECUTE IMMEDIATE ‘GRANT INSERT ON ‘||r.owner||’.’||r.object_name||’ TO ‘ || grantee;     END LOOP; END; /

Granting UPDATE on All Tables

CREATE OR REPLACE PROCEDURE grant_update(     username VARCHAR2,     grantee VARCHAR2) AS   BEGIN     FOR r IN (         SELECT owner, object_name         FROM dba_objects         WHERE owner = UPPER(username) AND OBJECT_TYPE = ‘TABLE’     )     LOOP         EXECUTE IMMEDIATE ‘GRANT UPDATE ON ‘||r.owner||’.’||r.object_name||’ TO ‘ || grantee;     END LOOP; END; /

Granting DELETE on All Tables

CREATE OR REPLACE PROCEDURE grant_delete(     username VARCHAR2,     grantee VARCHAR2) AS   BEGIN     FOR r IN (         SELECT owner, object_name         FROM dba_objects         WHERE owner = UPPER(username) AND OBJECT_TYPE = ‘TABLE’     )     LOOP         EXECUTE IMMEDIATE ‘GRANT DELETE ON ‘||r.owner||’.’||r.object_name||’ TO ‘ || grantee;     END LOOP; END;

Executing the Procedures

Once the procedures are created, we can grant privileges to specific users using simple PL/SQL execution. Test is username  on which all grant are being given & HOSPITAL is the schema name which holds all the objects for which grants are being given.

EXEC grant_delete(‘HOSPITAL’, ‘test’); EXEC grant_select(‘HOSPITAL’, ‘test’); EXEC grant_select_view(‘HOSPITAL’, ‘test’); EXEC grant_update(‘HOSPITAL’, ‘test’); EXEC grant_insert(‘HOSPITAL’, ‘test’);

Conclusion

Using these stored procedures, we can efficiently grant different types of privileges on all objects within a schema. This approach is particularly useful when dealing with multiple users and large schemas, saving time and ensuring consistency. You can modify these procedures to include additional privileges as per your requirements.