Home » SQL & PL/SQL » SQL & PL/SQL » PLSQL Procedure to Grant Privs on Schema Objects (Oracle Database 12.1.0.2 on Linux 6.9)
PLSQL Procedure to Grant Privs on Schema Objects [message #665383] Fri, 01 September 2017 16:02 Go to next message
Lucky A
Messages: 68
Registered: October 2007
Member
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 effective 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
Re: PLSQL Procedure to Grant Privs on Schema Objects [message #665385 is a reply to message #665383] Fri, 01 September 2017 18:32 Go to previous messageGo to next message
BlackSwan
Messages: 25745
Registered: January 2009
Location: SoCal
Senior Member

privileges acquired via ROLE do NOT apply withing named PL/SQL procedures.

One possible alternative is to write one or more procedure (SELECT_SCHEMA1) that is owned by schema owner & this procedure issues SELECT against needed schema table. Then all you need to do is issue GRANT like below
GRANT EXECUTE on SELECT_SCHEMA1 to SCHEMA2;

>This PLSQL Procedure would be scheduled to run constantly.
I disagree strongly.
When you manually CREATE USER NEW_SCHEMA3 you simply need to issue the necessary GRANTs immediately thereafter.

Why do so many different schemas need access to other schema's tables?
Is the whole "design" flawed unnecessarily?

Re: PLSQL Procedure to Grant Privs on Schema Objects [message #665390 is a reply to message #665385] Sat, 02 September 2017 07:38 Go to previous messageGo to next message
Lucky A
Messages: 68
Registered: October 2007
Member
Existing and new users are the ones that need access to the schemas' objects - to be able to query the tables owned by the various schemas.
Please give me example of your suggestions.

Thanks,

Lucky A
Re: PLSQL Procedure to Grant Privs on Schema Objects [message #665391 is a reply to message #665390] Sat, 02 September 2017 09:26 Go to previous messageGo to next message
BlackSwan
Messages: 25745
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: PLSQL Procedure to Grant Privs on Schema Objects [message #665393 is a reply to message #665390] Sun, 03 September 2017 07:16 Go to previous messageGo to next message
EdStevens
Messages: 863
Registered: September 2013
Senior Member
Lucky A wrote on Sat, 02 September 2017 07:38
Existing and new users are the ones that need access to the schemas' objects - to be able to query the tables owned by the various schemas.
Please give me example of your suggestions.

Thanks,

Lucky A
Most people here would rather teach you how to fish, rather than giving you a fish dinner. And to that end, are much more willing to help you show what you've tried for yourself.

What part of the proposed PL/SQL are you having difficulty with?

I'll give you a hint on the basis of it. Use SQL to create sql:

select 'grant select on ' || owner || '.' || table name || ' to someuser'
      from dba_tables
      where owner in target_schema;
Re: PLSQL Procedure to Grant Privs on Schema Objects [message #665405 is a reply to message #665393] Mon, 04 September 2017 14:37 Go to previous messageGo to next message
Lucky A
Messages: 68
Registered: October 2007
Member
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
Re: PLSQL Procedure to Grant Privs on Schema Objects [message #665406 is a reply to message #665405] Mon, 04 September 2017 14:47 Go to previous messageGo to next message
BlackSwan
Messages: 25745
Registered: January 2009
Location: SoCal
Senior Member
SQL> 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;  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31   32   33   34   35   36   37   38   39   40   41   42   43   44   45   46   47   48  
 49  /

Warning: Procedure created with compilation errors.

SQL> show error
Errors for PROCEDURE LUCKY_PROC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/3	 PLS-00103: Encountered the symbol "DECLARE" when expecting one of
	 the following:
	 ( ; is with default authid as cluster compress order using
	 compiled wrapped external deterministic parallel_enable
	 pipelined result_cache accessible rewrite

SQL> 

Re: PLSQL Procedure to Grant Privs on Schema Objects [message #665412 is a reply to message #665406] Tue, 05 September 2017 04:12 Go to previous message
cookiemonster
Messages: 12957
Registered: September 2008
Location: Rainy Manchester
Senior Member
Declare shouldn't be used in procedures and functions, it's implicit that the section between CREATE and BEGIN is the declare section.
Once you've fixed that there are other obvious problems:
1) You're not doing anything with the data fetched from the cursor.
2) There's no link between the inner and out queries, which I doubt it right.
3) The inner query is hard-coding the users in the output
4) That minus isn't going to work - there will never be any matches between the two parts for minus to eliminate as what you are selecting in the first is completely different to what you are selecting in the second.
Previous Topic: Select u'' from dual; What does u mean? in this sql.
Next Topic: how to extract data from oracle table having space in column name using vba code?
Goto Forum:
  


Current Time: Thu Dec 14 04:54:47 CST 2017

Total time taken to generate the page: 0.10627 seconds