Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Closed TopicStart new topic
> PLSQL Procedure to Grant Privs on Schema Objects
Lucky A
post Sep 1 2017, 02:13 PM
Post #1


Member
**

Group: Members
Posts: 25
Joined: 2-October 07
Member No.: 12,563



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
Go to the top of the page
 
+Quote Post
burleson
post Sep 2 2017, 12:12 PM
Post #2


Advanced Member
***

Group: Members
Posts: 13,529
Joined: 26-January 04
Member No.: 13



Hi Lucky,

Be careful, is this a classified database?

I would just grant select to public:

http://www.dba-oracle.com/art_builder_grant_sec.htm


--------------------
Hope this helps . . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
Lucky A
post Sep 4 2017, 02:35 PM
Post #3


Member
**

Group: Members
Posts: 25
Joined: 2-October 07
Member No.: 12,563



QUOTE (burleson @ Sep 2 2017, 01:12 PM) *
Hi Lucky,

Be careful, is this a classified database?

I would just grant select to public:

http://www.dba-oracle.com/art_builder_grant_sec.htm



Hi,

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. Any input would be appreciated.

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
Go to the top of the page
 
+Quote Post
boobal_ganesan
post Sep 5 2017, 08:29 AM
Post #4


Advanced Member
***

Group: Members
Posts: 77
Joined: 6-June 16
From: India
Member No.: 51,370



Hello Lucky,

Can you please try the below procedure for your requirement?

CODE
CREATE OR REPLACE PROCEDURE LUCKY_PROC
IS
BEGIN
FOR i IN
(SELECT object_name
FROM DBA_objects
WHERE owner ='SCHEMA_NAME'
AND object_type IN ('TABLE','VIEW') /*This loop gives you the objects that has to be granted to the users*/
)
LOOP
FOR j IN
(SELECT username
FROM all_users
WHERE username NOT IN ('SCHEMA1', 'SCHEMA2', 'SCHEMAN' ) /*This loop gives you the list of users, omitting the users which has a default grant of their own*/
)
LOOP
EXECUTE immediate 'Grant SELECT on '||i.object_name||' to '||j.username;
END LOOP j;
END LOOP i;
END;
/



Let me know if you need anything else.


Thank you,
Boobal Ganesan


--------------------
Check out my new Advanced PL/SQL book here - http://www.rampant-books.com/book_1701_pls...initive_ref.htm
Go to the top of the page
 
+Quote Post
Lucky A
post Sep 5 2017, 11:39 AM
Post #5


Member
**

Group: Members
Posts: 25
Joined: 2-October 07
Member No.: 12,563



QUOTE (boobal_ganesan @ Sep 5 2017, 09:29 AM) *
Hello Lucky,

Can you please try the below procedure for your requirement?

CODE
CREATE OR REPLACE PROCEDURE LUCKY_PROC
IS
BEGIN
FOR i IN
(SELECT object_name
FROM DBA_objects
WHERE owner ='SCHEMA_NAME'
AND object_type IN ('TABLE','VIEW') /*This loop gives you the objects that has to be granted to the users*/
)
LOOP
FOR j IN
(SELECT username
FROM all_users
WHERE username NOT IN ('SCHEMA1', 'SCHEMA2', 'SCHEMAN' ) /*This loop gives you the list of users, omitting the users which has a default grant of their own*/
)
LOOP
EXECUTE immediate 'Grant SELECT on '||i.object_name||' to '||j.username;
END LOOP j;
END LOOP i;
END;
/



Let me know if you need anything else.


Thank you,
Boobal Ganesan



Boobal,

Thanks a million!

Best,

Lucky
Go to the top of the page
 
+Quote Post
boobal_ganesan
post Sep 5 2017, 01:48 PM
Post #6


Advanced Member
***

Group: Members
Posts: 77
Joined: 6-June 16
From: India
Member No.: 51,370



QUOTE (Lucky A @ Sep 5 2017, 10:09 PM) *
Boobal,

Thanks a million!

Best,

Lucky


You're welcome, Lucky!


Regards,
Boobal Ganesan


--------------------
Check out my new Advanced PL/SQL book here - http://www.rampant-books.com/book_1701_pls...initive_ref.htm
Go to the top of the page
 
+Quote Post
Lucky A
post Sep 12 2017, 05:22 PM
Post #7


Member
**

Group: Members
Posts: 25
Joined: 2-October 07
Member No.: 12,563



Hi,

The customer's request is to write a PROCEDURE to list the tables owned by a schema and check whether users have SELECT privilege on the tables. If the user do not have READ privilege on the tables then the user should be granted the READ access (SELECT privilege). But if the user has access to the tables, no need for the GRANT privilege. If the priv is mistakenly revoked, it should be granted back to the user. Based on the level of the user, the priv granted could be (SELECT, UPDATE, INSERT, DELETE).

The updated PROCEDURE I've written is not producing the requested result. Please, I need your assistance:


CREATE OR REPLACE PROCEDURE LUCKY_PROC IS
CURSOR c_tablelist IS

SELECT username FROM all_users WHERE username like 'SCHEMANAME%';

CURSOR p_users (l_username in varchar2) IS

SELECT owner, object_name
from
(
SELECT owner,object
FROM all_objects
WHERE object_owner = l_username
and object_type in ('TABLE','VIEW')

minus

SELECT owner, table_name
from all_tab_privs
where grantee = l_username || '_user_role'
and privilege = 'SELECT'
)

v_sql_stmt := ('GRANT SELECT ON :b1.:b2');

BEGIN

FOR c1 IN c_tablelist_rec LOOP

FOR p1(c_tablelist_rec.username) IN p_users_rec LOOP

EXECUTE immediate v_sql_stmt using p_users_rec.OWNER, p_users_rec.OBJECT_NAME;

END LOOP;

END LOOP;

END;
/


Thanks,

Lucky
Go to the top of the page
 
+Quote Post
boobal_ganesan
post Sep 13 2017, 05:02 AM
Post #8


Advanced Member
***

Group: Members
Posts: 77
Joined: 6-June 16
From: India
Member No.: 51,370



Hello Lucky,

There were few issues in the procedure, which I managed to fix. Here is the working procedure for your requirement.


CODE
CREATE OR REPLACE PROCEDURE LUCKY_PROC
IS
CURSOR c_tablelist
IS
SELECT username FROM all_users WHERE username LIKE 'SCHEMANAME%';
CURSOR p_users (l_username IN VARCHAR2)
IS
SELECT owner,
object_name
FROM
(SELECT owner,
object_name
FROM all_objects
WHERE owner = l_username
AND object_type IN ('TABLE','VIEW')
MINUS
SELECT grantor,
table_name
FROM all_tab_privs
WHERE grantee = l_username
|| '_user_role'
AND privilege = 'SELECT'
);
v_sql_stmt VARCHAR2(100):= 'GRANT SELECT ON :b1.:b2';
BEGIN
FOR c1 IN c_tablelist
LOOP
FOR p1 IN p_users(c1.username)
LOOP
EXECUTE immediate v_sql_stmt USING p1.OWNER,
p1.OBJECT_NAME;
END LOOP p1;
END LOOP c1;
END;



Thank you,
Boobal Ganesan


--------------------
Check out my new Advanced PL/SQL book here - http://www.rampant-books.com/book_1701_pls...initive_ref.htm
Go to the top of the page
 
+Quote Post
Lucky A
post Sep 13 2017, 10:47 AM
Post #9


Member
**

Group: Members
Posts: 25
Joined: 2-October 07
Member No.: 12,563



QUOTE (boobal_ganesan @ Sep 13 2017, 06:02 AM) *
Hello Lucky,

There were few issues in the procedure, which I managed to fix. Here is the working procedure for your requirement.


CODE
CREATE OR REPLACE PROCEDURE LUCKY_PROC
IS
CURSOR c_tablelist
IS
SELECT username FROM all_users WHERE username LIKE 'SCHEMANAME%';
CURSOR p_users (l_username IN VARCHAR2)
IS
SELECT owner,
object_name
FROM
(SELECT owner,
object_name
FROM all_objects
WHERE owner = l_username
AND object_type IN ('TABLE','VIEW')
MINUS
SELECT grantor,
table_name
FROM all_tab_privs
WHERE grantee = l_username
|| '_user_role'
AND privilege = 'SELECT'
);
v_sql_stmt VARCHAR2(100):= 'GRANT SELECT ON :b1.:b2';
BEGIN
FOR c1 IN c_tablelist
LOOP
FOR p1 IN p_users(c1.username)
LOOP
EXECUTE immediate v_sql_stmt USING p1.OWNER,
p1.OBJECT_NAME;
END LOOP p1;
END LOOP c1;
END;



Thank you,
Boobal Ganesan



Thanks Boobal! The Procedure compiled successfully. I'm waiting for the customer to test it and get back to me whether it produced the intended result.

Thanks again!
Go to the top of the page
 
+Quote Post
Lucky A
post Sep 14 2017, 11:44 AM
Post #10


Member
**

Group: Members
Posts: 25
Joined: 2-October 07
Member No.: 12,563



Hi,

The procedure executes successfully but it doesn't grant the SELECT priv - no changes is made. I can't pinpoint why.

Thanks,

Lucky
Go to the top of the page
 
+Quote Post
burleson
post Sep 15 2017, 12:02 PM
Post #11


Advanced Member
***

Group: Members
Posts: 13,529
Joined: 26-January 04
Member No.: 13



Hi Lucky,

Our volunteers are not here to perform work for people!

This forum is for asking questions!

If you want someone to help you solve a problem, open a service request on MOSC

Http://support.oracle.com

You pay for oracle support services, they should be your primary contact!


--------------------
Hope this helps . . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Closed TopicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 22nd September 2017 - 05:27 PM