PLSQL Procedure to Grant Privs on Schema Objects [message #665383] |
Fri, 01 September 2017 16:02 |
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 #665405 is a reply to message #665393] |
Mon, 04 September 2017 14:37 |
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 |
|
BlackSwan
Messages: 26766 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 |
cookiemonster
Messages: 13925 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.
|
|
|