PLSQL Procedure to Grant Privs on Schema Objects

[Lucky A]

Hi to all,

We have a client that's in the middle of a big project - collecting data from different sources and loading the data into an Oracle 12.1.0.2 database running on Linux 6.9. New schemas are being created and so are the new users accounts. The users are granted GRANT SELECT privileges on all schemas tables and Views. A ROLE has not
been affective as many new schemas and objects are being created. We are constantly updating the ROLE. To avoid manually running the GRANT privileges, I need to create an Oracle PLSQL Procedure that would GRANT the SELECT privileges on the schemas TABLES and VIEWS objects to the users. This PLSQL Procedure would be scheduled to run constantly.

How best could I go about this? Any assistance would be greatly appreciated.

Thank you,

Lucky A

[Lucky A]

Hi to all,

We have a client that's in the middle of a big project - collecting data from different sources and loading the data into an Oracle 12.1.0.2 database running on Linux 6.9. New schemas are being created and so are the new users accounts. The users are granted GRANT SELECT privileges on all schemas tables and Views. A ROLE has not
been affective as many new schemas and objects are being created. We are constantly updating the ROLE. To avoid manually running the GRANT privileges, I need to create an Oracle PLSQL Procedure that would GRANT the SELECT privileges on the schemas TABLES and VIEWS objects to the users. This PLSQL Procedure would be scheduled to run constantly.

How best could I go about this? Any assistance would be greatly appreciated.

Thank you,

Lucky A

I'm trying to create a PROCEDURE to grant read privs to new users but at the same time the existing users will keep the privs already granted. There are about thenty_five (25) schemas and ten(10) active users who need to have SELECT priv on the schemas' tables and views. The below PROCEDURE I've written is not compiling successfully.

CREATE OR REPLACE PROCEDURE LUCKY_PROC
DECLARE
r_owner VARCHAR2(60);
r_name VARCHAR2(60);
t_name VARCHAR2(60);

CURSOR c_tabowner IS
SELECT owner, table_name FROM all_tables WHERE owner IN ('SCHEMA1', 'SCHEMA2', 'SCHEMAN');
CURSOR p_grantprivs IS
SELECT 'GRANT SELECT ON OWNER.'|| TABLE_NAME ||'to user_name1;'
FROM DBA_TABLES
WHERE owner='SCHEMA_NAME'
AND object_type IN ('TABLE','VIEW')
ORDER BY 1,2,3)
MINUS
SELECT grantee, granted_role
FROM dba_role_privs
WHERE owner IN ('SCHEMA1', 'SCHEMA2', 'SCHEMAN');
BEGIN
OPEN c_tabowner;
LOOP
FETCH c_tabowner INTO r_owner, r_name;
OPEN p_grantprivs;
LOOP
FETCH p_grantprivs INTO t_name;
CLOSE p_grantprivs;

END LOOP;
CLOSE c_tabowner;
END;
/


Thanks,

Lucky