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.
