dynamic sql to grant privilege [message #657997] |
Wed, 30 November 2016 02:13 |
|
raj85844
Messages: 26 Registered: November 2016 Location: chennai
|
Junior Member |
|
|
Hi Team,
Here i tried to dynamically get the schema name as input and trying to grant select privilege to the table that are created daily
please correct me on the mistake
CREATE OR REPLACE PROCEDURE "sp_grant_table_access" (
schema_name IN VARCHAR2 ())
As
cursor C_TNAME is select object_name from ALL_OBJECTS where object_type='TABLE' and owner= schema_name and created=sysdate;
v_grant varchar2(100);
begin
---select distinct owner into schema_name
----from all_objects ;
for i in C_TNAME
loop
v_grant :='GRANT SELECT ON '||i.object_name||' to '||i.schema_name;
execute immediate v_grant;
end loop;
dbms_output.put_line(sql%rowcount || 'No of rows are granted with the select privilege'||schema_name);
end;
</code>
--moderator update: corrected the [code] tags.
[Updated on: Wed, 30 November 2016 02:18] by Moderator Report message to a moderator
|
|
|
|
|
|
|
|
|
Re: dynamic sql to grant privilege [message #658012 is a reply to message #657997] |
Wed, 30 November 2016 07:53 |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
And creating an object in double quotes will force you to have to use it with double quotes and lower case all the time. You cannot just simply call it as sp_grant_table_access.
|
|
|
|
|
|
Re: dynamic sql to grant privilege [message #658021 is a reply to message #658020] |
Wed, 30 November 2016 13:49 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
The following code will grant the select to the table to an entered SCHEMA name if it is not already granted
CREATE OR REPLACE PROCEDURE Sp_grant_table_access (Schema_name IN VARCHAR2)
AS
Cnt NUMBER;
BEGIN
Cnt := 0;
FOR I
IN (SELECT Object_name
FROM User_objects A
WHERE Object_type = 'TABLE'
AND NOT EXISTS
(SELECT NULL
FROM User_tab_privs B
WHERE B.Owner = UPPER (Schema_name)
AND B.Table_name = A.Object_name
AND B.Privilege = 'SELECT'))
LOOP
EXECUTE IMMEDIATE
'GRANT SELECT ON ' || I.Object_name || ' to ' || Schema_name;
Cnt := Cnt + 1;
END LOOP;
DBMS_OUTPUT.Put_line (
Cnt
|| 'No of rows are granted with the select privilege'
|| Schema_name);
END Sp_grant_table_access;
[Updated on: Wed, 30 November 2016 14:05] Report message to a moderator
|
|
|
|
Re: dynamic sql to grant privilege [message #658060 is a reply to message #658021] |
Thu, 01 December 2016 14:35 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Bill B wrote on Wed, 30 November 2016 14:49The following code will grant the select to the table to an entered SCHEMA name if it is not already granted
Why spend time of "is it already granted"? And to clarify your code checks if it is already granted directly, since it can be granted via role.
SY.
|
|
|
|
|
|
Re: dynamic sql to grant privilege [message #658528 is a reply to message #658527] |
Fri, 16 December 2016 05:24 |
|
raj85844
Messages: 26 Registered: November 2016 Location: chennai
|
Junior Member |
|
|
This is a data warehousing project and there are many permanent tables with huge volume of data's ( 90 Million+ records)
and as a tactical solution(on performance aspect) in spite of hitting the big tables every day Around 5 to 10 table are created on daily (and the structure of the table changes as per the rules that are set in front end and these tables are created and dropped from front end only select privileges are given in oracle )
and from these tables some analytical operations are carried and shared to other schema
|
|
|