Home » SQL & PL/SQL » SQL & PL/SQL » Generalized stored procedure to truncate some tables owned by another Schema
Generalized stored procedure to truncate some tables owned by another Schema [message #233884] Fri, 27 April 2007 20:27 Go to next message
A.M. Craig
Messages: 3
Registered: April 2007
Junior Member
Since there's no way to grant a login the right to truncate specific tables owned by another login/schema, I would like a table-driven generalized procedure to truncate tables owned by a main userid <MainUser>.

That is, I would like to be able to maintain a driver table <DTable> that has 2 columns -- userid and tablename. If I then grant the procedure to another userid (say <DWJob>) -- then <DWUser> could use the procedure, invoking a tablename <TName> -- and if there is a row in <DTable> with values <DWJob>,<TName> -- then the procedure would truncate the table.

I've been told that I could write one-up procedures like this to do it for a single table:

PROCEDURE truncate_table (owner VARCHAR2, table_name VARCHAR2)
AS
v_sql VARCHAR2 (2000);
BEGIN
v_sql := 'truncate table ' || owner || '.' || table_name;
# include a table verification here if necessary
EXECUTE IMMEDIATE v_sql;
END truncate_table;


So -- it's the unexplained "include a table verification here" that I'd appreciate some assistance on.

If you have any pretty additions that print out one or more messages at the end, those, too, would be appreciated.

Thanks!

[Updated on: Fri, 27 April 2007 20:32]

Report message to a moderator

Re: Generalized stored procedure to truncate some tables owned by another Schema [message #233886 is a reply to message #233884] Fri, 27 April 2007 20:56 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Based upon the fact that this is your 1st post, you are asking to provide a cability to work around built in Oracle security without knowing how to totally idiotproof your software is a recipe for disaster.

Does each "user" have their own Oracle schema?


>If you have any pretty additions that print out one or more messages at the end

Print out via what user Interface?
There is typically some sort of interface between the end user & internal Oracle package(s).
Re: Generalized stored procedure to truncate some tables owned by another Schema [message #234272 is a reply to message #233886] Mon, 30 April 2007 12:18 Go to previous messageGo to next message
A.M. Craig
Messages: 3
Registered: April 2007
Junior Member
We are migrating data-warehousing types of tables from Sybase to Oracle. We are, if you will, a data mart that "pulls" most of our data from various sources, except for one or two. There has been a bit of separation between my group and the actual data warehouse team -- they are only updating a subset of our tables.

So there should only be three or four userids which are updating tables. No regular users have the rights to create tables nor do inserts/updates/deletes. They don't even have SELECT rights on most of our tables. We build READ-ONLY views which we selectively grant to groups of regular userids.

Our main schema is owned by a userid which doesn't even have DBO rights. I had asked our DBA for information about how to handle our situation, and he suggested the stub of the procedure I'd posted.

So -- what I wanted to do was to build a stored procedure in "my" schema which I would only grant to these three or four data-warehouse-group userids. They will be using PowerCenter to update tables that are all part of "my" central schema. I would like to be able to grant each userid, on a table-by-table basis -- which tables in the central schema that they could truncate as part of their PowerCenter jobs.

So -- if the procedure is only granted to specific users -- and if the procedure validates against a table that I maintain -- what is the security hole?


As for the messages -- I could live without messages. I just thought it would be good to echo out something if the table being called was not valid for that userid ("not authorized to truncate table", or something like that).
Re: Generalized stored procedure to truncate some tables owned by another Schema [message #234315 is a reply to message #234272] Mon, 30 April 2007 16:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So create the procedure(s) in the target schema(s) and remove the "owner" part.
Create a table that records userid associated with table_name to check in your procedure if the call is allowed.
Use raise_application_error function to send an error to the caller if this is a forbidden action.

Regards
Michel
Re: Generalized stored procedure to truncate some tables owned by another Schema [message #234319 is a reply to message #234315] Mon, 30 April 2007 16:26 Go to previous messageGo to next message
A.M. Craig
Messages: 3
Registered: April 2007
Junior Member
Since my DBA wouldn't tell me how to build the "validation" part -- that's what I was hoping someone would tell me -- since I'm a newbie at building PL/SQL stored procedures in Oracle. How do I even know the calling userid?
Re: Generalized stored procedure to truncate some tables owned by another Schema [message #234320 is a reply to message #234319] Mon, 30 April 2007 16:34 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
create table check (username varchar2(30), tablename varchar2(30));
Fill the table with name of user and tables he is allowed to truncate.

In your procedure, declare a variable cnt and use:
select count(*) into cnt from check where username=user and tablename=table_name;
if cnt>0 then
execute immediate 'truncate table '||table_name';
else
raise_application_error(-20000,'You are not allowed to truncate this table');
end if;

This is just an example.

Regards
Michel

[Updated on: Mon, 30 April 2007 16:35]

Report message to a moderator

Previous Topic: Top 5 salary by departments
Next Topic: What is LMS?
Goto Forum:
  


Current Time: Fri Dec 09 17:43:13 CST 2016

Total time taken to generate the page: 0.33615 seconds