PLSQL Procedure to Grant Privs on Schema Objects
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: PLSQL Procedure to Grant Privs on Schema Objects

  1. #1
    Join Date
    Oct 2007
    Posts
    37

    PLSQL Procedure to Grant Privs on Schema Objects

    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

  2. #2
    Join Date
    Oct 2007
    Posts
    37
    Quote Originally Posted by Lucky A View Post
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width