Home » SQL & PL/SQL » SQL & PL/SQL » dynamic sql to grant privilege (11g)
dynamic sql to grant privilege [message #657997] Wed, 30 November 2016 02:13 Go to next message
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 #657999 is a reply to message #657997] Wed, 30 November 2016 02:17 Go to previous messageGo to next message
John Watson
Messages: 7618
Registered: January 2010
Location: Global Village
Senior Member
Your created=sysdate predicate is not going to select anything. SYSDATE gives the time as well as the date.
Re: dynamic sql to grant privilege [message #658000 is a reply to message #657997] Wed, 30 November 2016 02:21 Go to previous messageGo to next message
Littlefoot
Messages: 21439
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well,
and created = sysdate
is doomed to failure. I guess you should TRUNC these dates.

SQL%ROWCOUNT won't do anything either in this context.
Re: dynamic sql to grant privilege [message #658001 is a reply to message #658000] Wed, 30 November 2016 02:31 Go to previous messageGo to next message
raj85844
Messages: 26
Registered: November 2016
Location: chennai
Junior Member
yes sure will change it to " trunc(created)=trunc(sysdate) "

I don't know how should i have to declare the schema inside the cursor

"Error(16,56): PLS-00302: component 'SCHEMA_NAME' must be declared"
Re: dynamic sql to grant privilege [message #658002 is a reply to message #658001] Wed, 30 November 2016 02:59 Go to previous messageGo to next message
Michel Cadot
Messages: 65844
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Remove "i.".

Re: dynamic sql to grant privilege [message #658004 is a reply to message #658002] Wed, 30 November 2016 03:13 Go to previous messageGo to next message
Littlefoot
Messages: 21439
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Besides, it would be easier to read your code if you read best practices and name parameters differently (such as "p_schema_name" or "par_schema_name" or "schema_name_in" or whatever those best practices suggest) because - when it is "schema_name", which is equal to ALL_OBJECTS' column name ("schema_name"), you might get confused. Actually, you got confused.
Re: dynamic sql to grant privilege [message #658011 is a reply to message #657997] Wed, 30 November 2016 06:47 Go to previous messageGo to next message
EdStevens
Messages: 979
Registered: September 2013
Senior Member
raj85844 wrote on Wed, 30 November 2016 02:13
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
"table that are created daily"?????

Why are tables being created "daily". That, in and of itself, suggests a very, very serious design flaw in the system.
Re: dynamic sql to grant privilege [message #658012 is a reply to message #657997] Wed, 30 November 2016 07:53 Go to previous messageGo to next message
joy_division
Messages: 4901
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 #658018 is a reply to message #658011] Wed, 30 November 2016 08:41 Go to previous messageGo to next message
raj85844
Messages: 26
Registered: November 2016
Location: chennai
Junior Member
As the requirement is such that the tables will be created and the same will be dropped day end
So I required to give the privileges for the table based on the user(schema) hence seemingly guidance on this if you could really assist then it's a big treat for me thanks
Re: dynamic sql to grant privilege [message #658019 is a reply to message #658018] Wed, 30 November 2016 08:47 Go to previous messageGo to next message
BlackSwan
Messages: 26193
Registered: January 2009
Location: SoCal
Senior Member
the design is fatally flawed.
Application objects should be known & static between application version software releases.

Re: dynamic sql to grant privilege [message #658020 is a reply to message #658018] Wed, 30 November 2016 10:15 Go to previous messageGo to next message
Michel Cadot
Messages: 65844
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Why not truncate the table instead?

Re: dynamic sql to grant privilege [message #658021 is a reply to message #658020] Wed, 30 November 2016 13:49 Go to previous messageGo to next message
Bill B
Messages: 1801
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 #658045 is a reply to message #658018] Thu, 01 December 2016 06:35 Go to previous messageGo to next message
EdStevens
Messages: 979
Registered: September 2013
Senior Member
raj85844 wrote on Wed, 30 November 2016 08:41
As the requirement is such that the tables will be created and the same will be dropped day end
No, that is not a requirement. That is a (flawed) technical solution to a requirement.

At the very least, instead of dropping and recreating the table, you could do as Michael Cadot suggested and simply truncate the table.


Re: dynamic sql to grant privilege [message #658060 is a reply to message #658021] Thu, 01 December 2016 14:35 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2806
Registered: January 2010
Location: Connecticut, USA
Senior Member
Bill B wrote on Wed, 30 November 2016 14:49
The 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 #658138 is a reply to message #658060] Mon, 05 December 2016 08:26 Go to previous messageGo to next message
Bill B
Messages: 1801
Registered: December 2004
Senior Member
I also think that it should be enforced by using a role, but I showed the OP how to do what they requested.
Re: dynamic sql to grant privilege [message #658526 is a reply to message #658138] Fri, 16 December 2016 05:00 Go to previous messageGo to next message
raj85844
Messages: 26
Registered: November 2016
Location: chennai
Junior Member
Thanks a lot Bill for understanding and sharing the piece of code,
As i tried the code and it is working fine ( good learning experience )

As part of the project the table created daily are a new tables day by day and the same tables will not be retained again in db (so truncate is not done )
and these table life span is just day

Cheers
Re: dynamic sql to grant privilege [message #658527 is a reply to message #658526] Fri, 16 December 2016 05:05 Go to previous messageGo to next message
cookiemonster
Messages: 13282
Registered: September 2008
Location: Rainy Manchester
Senior Member
So you have a new table every day and the old table is dropped?
Do these tables have the same structure every day?
Re: dynamic sql to grant privilege [message #658528 is a reply to message #658527] Fri, 16 December 2016 05:24 Go to previous message
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
Previous Topic: Extract directory path from a datafile name
Next Topic: how to generate an email on a database event?
Goto Forum:
  


Current Time: Thu Oct 18 04:35:36 CDT 2018