base privs
A reader, September 23, 2001 - 8:57 pm UTC
Tom
"Since stored procedures run with the base privs of the OWNER of the procedure"
Can you elaborate on your above statement
September 24, 2001 - 7:37 am UTC
It is known as "definers rights" (there is another mode procedures may run in "invokers rights" as well but definers is the default).
This is one of the most powerful features of a stored procedure -- it is what makes them more then useful. A stored procedure by default runs with the privilege set (minus roles) of the DEFINER of the procedure. This means I can write a procedure like:
create procedure insert_into_t( p_data in varchar2 )
as
begin
-- whatever logic I want to code to ensure the current USER is
-- allowed to insert
-- whatever complex validation of data I want to do
-- whatever auditing I want to do
insert into t values ( p_data );
end;
And then grant execute on insert_into_t to SCOTT. Now SCOTT can insert into table T via this procedure -- but SCOTT cannot insert into T directly (and hence bypass my authorization logic, my validation, my auditing and so on).
You can take this a step further with REF CURSORS -- allowing the user to get an OPEN cursor to a result set you want them to see -- without having to give them TOTAL select. For example you might code:
create procedure get_emp_data( p_cursor in out types.rc_type )
as
begin
if ( user in ( 'ADMIN', 'TKYTE', 'SYSTEM' ) )
then
open p_cursor for select ename, sal, job from emp;
else
open p_cursor for select ename, to_number(null) sal, job from emp;
end if;
end;
/
Now, if the user is one of the "special" users - they can see the sal column, else they just see NULL.
Reply for Review 1
Senthil, September 24, 2001 - 12:30 am UTC
The stored procedure runs with the privilege of the table owner it will trucante the table. Since the other user
is granted with the execute privilege on the procedure, when the procedure executed the table will get truncated.
What if a stored procedures is to be created with their by someone other then owner
A reader, August 15, 2002 - 5:36 pm UTC
Say i have a user a who is the owner of all objects
I use only user b to create objects(stored procedure, functions, seqeunces, table) etc for the user a
This case ussually happens when u have external consultants doing development and you can't give them the object owner id and password , but still allow them to create objects in user a and audit all their statements
August 15, 2002 - 7:33 pm UTC
same answer.
create or repalce procedure create_a_procedure_for_a( p_stmt in varchar2 )
as
begin
execute immediate 'create or replace procedure ' || p_stmt;
end;
/
create execute on create_a_procedure_for_a to b;
as b:
begin
create_a_procedure_for_a( '
p
as
begin
dbms_output.put_line( ''Hello there'' );
end;
');
end;
Or, have them create/test the code in their own database (as A) and then pass the code along to you for installation -- so you know you can
a) install it
b) have the code for when they walk away and still be able to install it
I like the second option personally.
(and if I was a smart consultant, I would be able to gain access to A's account in about 30 seconds if you grant me execute on create_a_procedure_for_a but that is an exercise left to the reader)
Drop Any Table
Colin McLennan, April 22, 2005 - 4:59 am UTC
Tom,
Having granted the drop any table privilige to a user I still cannot truncate the table and get the following "ORA-00942: table or view does not exist". I can't quite figure out what is going on here as the table has a public synonym and i can describe the table.
Any help much appreciated.
Colin.
April 22, 2005 - 10:32 am UTC
you cannot truncate a synonym.
truncate table OWNER.TABLE_NAME
should work with the user with drop any table (that extremely powerful privilege)
ops$tkyte@ORA9IR2> create user a identified by a;
User created.
ops$tkyte@ORA9IR2> grant drop any table, create session, create synonym to a;
Grant succeeded.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> drop table t;
Table dropped.
ops$tkyte@ORA9IR2> create table t ( x int );
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> @connect a/a
ops$tkyte@ORA9IR2> set termout off
a@ORA9IR2> set termout on
a@ORA9IR2> create synonym t for ops$tkyte.t;
Synonym created.
a@ORA9IR2> truncate table t;
truncate table t
*
ERROR at line 1:
ORA-00942: table or view does not exist
a@ORA9IR2> truncate table ops$tkyte.t;
Table truncated.
A different problem
Colin McLennan, May 13, 2005 - 9:40 am UTC
Tom,
Thanks for that. Initial problem solved but have another which is baffling me. User A can truncate tables owned by user B via a stored procedure (as described above). However, user A now needs to truncate a table owned by user C. User B has DROP ANY TABLE privilege and can truncate the table owned by C (truncate table c.table_name). However, when I use the stored procedure as either user A or B I get the "-942 ORA-00942: table or view does not exist" error. No synonyms on the table this time and prefixing the table name with "c.". I must have missed something but not quite sure what. Do I have to replicate the stored procedure for user C? Hope not because that would be a bit messy.
Thanks in anticipation.
Colin.
May 13, 2005 - 10:47 am UTC
give me example please.... not sure what the chain of events is.
ops$tkyte@ORA9IR2> create user a identified by a;
User created.
ops$tkyte@ORA9IR2> create user b identified by b;
User created.
ops$tkyte@ORA9IR2> create user c identified by c default tablespace users quota unlimited on users;
User created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> grant create session to a;
Grant succeeded.
ops$tkyte@ORA9IR2> grant create session, create procedure, drop any table to b;
Grant succeeded.
ops$tkyte@ORA9IR2> grant create session, create table to c;
Grant succeeded.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> @connect c/c
ops$tkyte@ORA9IR2> set termout off
c@ORA9IR2> set termout on
c@ORA9IR2> create table t (x int);
Table created.
c@ORA9IR2>
c@ORA9IR2> @connect b/b
c@ORA9IR2> set termout off
b@ORA9IR2> set termout on
b@ORA9IR2> create procedure trunc_c
2 as
3 begin
4 execute immediate 'truncate table c.t';
5 end;
6 /
Procedure created.
b@ORA9IR2> exec trunc_c
PL/SQL procedure successfully completed.
b@ORA9IR2> grant execute on trunc_c to a;
Grant succeeded.
b@ORA9IR2> @connect a/a
b@ORA9IR2> set termout off
a@ORA9IR2> set termout on
a@ORA9IR2> exec b.trunc_c
PL/SQL procedure successfully completed.
Roles
Colin McLennan, May 17, 2005 - 6:32 am UTC
Tom,
Although the user had the DROP ANY TABLE privilege it had been granted via a role. I changed this so it was granted explicitly to the user and all works fine now.
Sorry if this has wasted any of your time.
Colin.
May 17, 2005 - 9:11 am UTC
not at all -- that is why these test cases are so useful, if you cannot reproduce it in a controlled environment, there must be something else afoot.
A reader, March 09, 2006 - 12:29 pm UTC
A reader, June 05, 2006 - 12:25 pm UTC
best way to truncate table
Martina, July 20, 2006 - 5:24 am UTC
let's suppose i have table, which is regurarly truncated and then filled with approximately the same amount of data.
is it worth to use REUSE STORAGE option while truncating this table, will it improve performance?
thanks for your opinion
July 22, 2006 - 4:39 pm UTC
the only answer is:
it depends.
If and only if storage "allocation" is a thing that is responsible for a large amount of your load time.
It would not hurt in this case likely to reuse storage.
TRUNCATE TABLE
A reader, August 14, 2006 - 12:18 pm UTC
I am truncating a table in the same user schema and it is hanging. No error is coming even in the alert file.
while delete is working.
what could be the reason.
August 14, 2006 - 12:52 pm UTC
you could be waiting for IO to complete.
all dirty blocks in the cache must be flushed out.
you said "truncate" and "delete" as if they were synonymous. hope you meant truncate.
truncate table
A reader, August 14, 2006 - 12:59 pm UTC
It happened again the next day.
truncate hangs.
Now the table is empty and truncate is running perfectly fine.
It used to run run fine before also where there were records.
August 14, 2006 - 1:23 pm UTC
trace it, with wait events, you'll see what it was waiting on.
thanks
A reader, August 14, 2006 - 1:51 pm UTC
A reader, August 21, 2006 - 11:08 pm UTC
What if the procedure for truncating is embedded in a package
Aravind, May 23, 2007 - 1:00 am UTC
Hi Tom,
I created a user and granted him all the previleges to execute a package and procedures. I am able to execute all the procedures in the package except the one for truncate table. However when i created a stored procedure, I was able to truncate the table. Is this not possible when the procedure do_the_truncate resides in a package.
Aravind
May 23, 2007 - 6:57 am UTC
you did something wrong, without an example - we cannot say what.
ops$tkyte%ORA10GR2> create user a identified by a;
User created.
ops$tkyte%ORA10GR2> grant create session to a;
Grant succeeded.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table t (x int);
Table created.
ops$tkyte%ORA10GR2> insert into t values ( 1 );
1 row created.
ops$tkyte%ORA10GR2> commit;
Commit complete.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace package my_pkg
2 as
3 procedure do_truncate;
4 end;
5 /
Package created.
ops$tkyte%ORA10GR2> create or replace package body my_pkg
2 as
3 procedure do_truncate
4 as
5 begin
6 execute immediate 'truncate table t';
7 end;
8 end;
9 /
Package body created.
ops$tkyte%ORA10GR2> grant execute on my_pkg to a;
Grant succeeded.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select * from t;
X
----------
1
ops$tkyte%ORA10GR2> connect a/a
Connected.
a%ORA10GR2> exec ops$tkyte.my_pkg.do_truncate
PL/SQL procedure successfully completed.
a%ORA10GR2> connect /
Connected.
ops$tkyte%ORA10GR2> select * from t;
no rows selected
What if the procedure for truncating is embedded in a package
Aravind, May 23, 2007 - 7:37 am UTC
Yes Tom. I had run the package again in the user area. It was conflicting for some reason i think.
Later i dropped that package which was created in the User area.
Created a public Synonym and granted an execute on the package. Now it is working fine.
May 23, 2007 - 8:38 am UTC
the package is not relevant.
the truncate can be in a packaged procedure, a packaged function, a standalone procedure, a standalone function.
It does have to be created by a schema capable of doing the truncate OR it needs to be authid current_user (invoker rights) and executed by an appropriately privileged user.
Ok, since no one said it, I will
Mark Brady, May 25, 2007 - 3:50 pm UTC
(and if I was a smart consultant, I would be able to gain access to A's account in about 30 seconds
if you grant me execute on create_a_procedure_for_a but that is an exercise left to the reader)
SQL INJECTION!
That being said, and yes it can be worked around, why not allow privs to be given from one schema to another?
GRANT CREATE TABLE IN schemaA TO schemaB
That's a feature I'd love to see added. Once you have Create Any, you may as well have Create 'Some'. It's bizarre that I can allow you to add your table's data to my tablespace but not your table's definition to my schema.
difference between truncate and drop table
Kamran Agayev, January 28, 2009 - 4:28 am UTC
Hi Tom
Can you explain please is there any difference between
truncate table my_table;
drop table my_table;
with
drop table my_table;
Will there be any better performance issue between them?
Thanks
January 28, 2009 - 3:35 pm UTC
well, one of them drops the table, the other does not
so, that is pretty huge.
what do you want to do?
truncate or drop?
when you answer that, we can answer you.
If you want to remove all data from the table, use truncate
If you want to drop the table so it no longer exists, use drop.
difference between truncate and drop table - is a confusing title
Marco, January 28, 2009 - 5:54 pm UTC
When I read carefully (which is necessary :p) I think Kamran wants to drop the table and ask if:
"truncate table" followed by "drop table"
differs from
"drop table"
Regards,
Vuurtje (The person who told you you are talking to much)
January 30, 2009 - 12:25 pm UTC
he didn't ask that. I only answer what is asked, I try not to infer things. If they want to come back and be more specific - great :)
Marco is right :)
Kamran Agayev, January 29, 2009 - 10:25 am UTC
Yes Marco is right :)
I want to ask what's the difference between
"truncate table" followed by "drop table"
and
"drop table"
is there any performance gain when we issue "truncate table" before "drop table"?
Thanks
January 30, 2009 - 2:28 pm UTC
let me add to the list that Greg has - both truncate and drop must verify the buffer cache is devoid of modified blocks for this action, and checkpoint them to disk if there are any - before proceeding, so you would be doing this twice too.
double the steps for many of the steps.
Now, can a truncate appear to make a drop go faster?
Sure - because the truncate might have taken a long time, to checkpoint those blocks and the drop goes faster because the truncate did that - but did the truncate+drop go faster? probably not.
actually, drop by itself in 10g would probably be faster :)
reason: recycle bin, it doesn't have to deallocate the extents right there and then.
Another really good reason NOT to truncate and then drop, you would not be able to recover your data in the event of a "right command, wrong database" scenario using flashback table to before drop
TRUNCATE and DROP vs. just DROP
Greg Smith, January 29, 2009 - 7:06 pm UTC
I would hazard a pretty safe guess that using the simple DROP by itself would have a significant performance advantage(as significant as a single non-data-row reading/manipulating DDL statement can be), in addition to obvious clarity advantages of having only one code line that does it all.
If you thing about it,
TRUNCATE would
A. PARSE,
B. Check for transaction and COMMIT (the standard implied pre-DDL commit)
C. Check for active foreign keys
D. Get an exclusive lock on the table
E. Gather info on all segments and extents for both indexes and the table (at the partition level)
F. Optionally convert all but the first extent of each segment to free space
G. Reset the HWM of all extents that remain (which might be all of them if space was retained), reset any freelist info if present, etc,
H. Post all DDL changes
I. Release all Locks
DROP would effectively have to start all over and repeat steps A, B, C, D, then gather info on ALL table related objects including the table and index segments and extents that TRUNCATE had to look at, but also including ALL DD objects (Columns, Comments, Constraints, Triggers, etc).
Then it would simply DROP everything, releasing ALL table and index table extents to freespace.
Then it would essentially repeat steps H and I.
So at the very least, steps A,B,C,D,probably all of E, all of F, H, and I would be completely repeated for no benefit.
And Step G from the truncate would just be redundant work that was completely unnecessary.
Add to that the fact that programs might come in between the TRUNCATE and DROP and actually unwittingly insert something between the TRUNCATE and DROP, and/or perhaps hold up your DROP for an extended period of time while you wait for a transaction to release the resources. Unlikely, but unpreventable, unless you use commit-preserved user-defined locks to prevent it, which of course would just be another overhead.
By the way, I am not trying to represent Oracle's internal method or sequence of carrying out a TRUNCATE or DROP; I'm just guessing based on my knowledge of what the two operations accomplish.
January 30, 2009 - 2:49 pm UTC
actually..... think 10g :)
recycle bin
drop would not have to do e,f,g - just puts object into recycle bin.
also, you missed an important step d.2 - checkpoint dirty blocks
but yes, good analysis...
Re: TRUNCATE and DROP vs. just DROP
Greg Smith, February 04, 2009 - 9:01 pm UTC
Tom, great points.
I still do not intuitively think about the Recycle Bin feature even though I've been using 10G for years -- and it has even saved me some hassle in dev databases while developing product conversion/upgrade scripts (Recycle Bin is fantastic for this if you have the room). Funny how long our old hard-learned knowledge gets in the way sometimes, even when our younger neurons know better.
Thanks for the clarifications!
One level more
AppSure, February 19, 2009 - 4:19 pm UTC
Hi tom,
Will it be possible to make this work across different user schema without the powerful grants, what I mean is will it be possible to do this way
User1, User2, User3,... UserN all have this same procedure (the do_the_truncate procedure) created on them and then execute granted to another user say TruncUSR given this scnario will it be possible for me to have another procedure in TruncUSR schema that I can use with the user name and table name so this wrapper resolves the correct procedure name and executes it in the respective owner schema, say call_do_the_truncate('USERNAME', 'TableName') will that be possible? Just curious to know - Thanks
February 21, 2009 - 8:29 pm UTC
if user1 and user2 each have a procedure dtt (do the truncate) that they grant to truncUser (directly to truncUser, so truncUser can in fact create a procedure themselves that call user1.dtt or user2.dtt) then yes, truncUser can create a procedure that would take the schema name and table name as input and then invoke that schema.dtt( pass_the_table_name );
ops$tkyte%ORA10GR2> create user u1 identified by u1 quota unlimited on users default tablespace users;
User created.
ops$tkyte%ORA10GR2> grant create session, create table, create procedure to u1;
Grant succeeded.
ops$tkyte%ORA10GR2> create user u2 identified by u2 quota unlimited on users default tablespace users;
User created.
ops$tkyte%ORA10GR2> grant create session, create table, create procedure to u2;
Grant succeeded.
ops$tkyte%ORA10GR2> create user tu identified by tu;
User created.
ops$tkyte%ORA10GR2> grant create session, create procedure to tu;
Grant succeeded.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> connect u1/u1
Connected.
u1%ORA10GR2> create table t as select * from all_users;
Table created.
u1%ORA10GR2> create or replace procedure dtt( p_tname in varchar2 )
2 as
3 begin
4 execute immediate 'truncate table ' || dbms_assert.sql_object_name( p_tname );
5 end;
6 /
Procedure created.
u1%ORA10GR2> grant execute on dtt to tu;
Grant succeeded.
u1%ORA10GR2> select count(*) from t;
COUNT(*)
----------
63
u1%ORA10GR2>
u1%ORA10GR2> connect u2/u2
Connected.
u2%ORA10GR2> create table t as select * from all_users;
Table created.
u2%ORA10GR2> create or replace procedure dtt( p_tname in varchar2 )
2 as
3 begin
4 execute immediate 'truncate table ' || dbms_assert.sql_object_name( p_tname );
5 end;
6 /
Procedure created.
u2%ORA10GR2> grant execute on dtt to tu;
Grant succeeded.
u2%ORA10GR2> select count(*) from t;
COUNT(*)
----------
63
u2%ORA10GR2>
u2%ORA10GR2> connect tu/tu
Connected.
tu%ORA10GR2> create or replace procedure dtt( p_schema in varchar2, p_tname in varchar2 )
2 as
3 begin
4 execute immediate 'begin ' || dbms_assert.schema_name(p_schema) || '.dtt( :x ); end;' using p_tname;
5 end;
6 /
Procedure created.
tu%ORA10GR2> grant execute on dtt to scott;
Grant succeeded.
tu%ORA10GR2>
tu%ORA10GR2> connect scott/tiger
Connected.
scott%ORA10GR2> exec tu.dtt( 'U1', 'T' );
PL/SQL procedure successfully completed.
scott%ORA10GR2> exec tu.dtt( 'U2', 'T' );
PL/SQL procedure successfully completed.
scott%ORA10GR2> exec tu.dtt( 'xxx', 'T' );
BEGIN tu.dtt( 'xxx', 'T' ); END;
*
ERROR at line 1:
ORA-44001: invalid schema
ORA-06512: at "SYS.DBMS_ASSERT", line 243
ORA-06512: at "TU.DTT", line 4
ORA-06512: at line 1
scott%ORA10GR2> exec tu.dtt( 'SCOTT', 'T' );
BEGIN tu.dtt( 'SCOTT', 'T' ); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'SCOTT.DTT' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
ORA-06512: at "TU.DTT", line 4
ORA-06512: at line 1
scott%ORA10GR2> connect u1/u1
Connected.
u1%ORA10GR2> select count(*) from t;
COUNT(*)
----------
0
Truncate table with SQL Loader
.net developer, February 19, 2010 - 9:39 am UTC
Tom
We have a .net application executing SQL loader using DB application user id (not schema owner). We need to truncate the staging table before loading. The control file has truncate option but this does not work from .net application due to insufficient privileges.
Do you recommend to
a) use truncate option in control file and grant privileges to application user id or
b) remove truncate option in control file, encapsulate truncate in stored proc and call this stored proc from .net app before executing SQL loader ?
Any other options ?
February 25, 2010 - 12:44 am UTC
a) no, because you would need to grant an ANY privilege to the account, that is way too much power.
b) yes.
Actually, I would go with option (c) - do not use sqlldr, use an external table - you can actually determine that an error occurred and do something about it that way. Running a program from a program is, well, pretty shaky.
truncate table
A reader, June 05, 2010 - 9:58 pm UTC
Hi Tom,
What are the situations where one command is preferred over the other?
1)TRUNCATE TABLE ... DROP STORAGE;
2)TRUNCATE TABLE ... RESUSE STORAGE;
3) TRUNCATE TABLE ...;
Regards,
June 09, 2010 - 8:13 am UTC
you would like the first one when you would like the storage to be released.
you would like the second one when you would like the storage to be retained.
you would like the third one when you like to use defaults (eg: you don't really care)
If you are truncating a table and then will slowly fill it over time, drop storage might make sense, you'll reclaim that space for a while.
If you are truncating a table with the goal of immediately refilling it, keeping the storage might make sense so as to avoid having to reallocate that which you just dropped.
Another option for consultant strategy
Galen Boyer, June 09, 2010 - 8:32 am UTC
Create them their own development schema on your instance. Create synonyms of
all objects they need, grant everything on those objects they would need (some
subset of the schema owner's privileges). Then, have them build the code,
without owner references in their code. And, if they also need to reference
objects outside the particular schema, you can have them put owner.xxx in their
codebase, or create synonyms for those objects as well. But, if you do, then
your owning schema will need those synonyms as well. Then, you should be able
to take their code and compile it into the actual schema and it should now be
running against the actual objects, instead of the synonyms. Then, they are
coding against the real tables, but do not have the power of that schema owner.
Privilege issue
Swamy, July 18, 2014 - 8:58 am UTC
Even after granting issue I am still facing issue, please advise.
create or replace procedure schema1.do_the_truncate
is
begin
EXECUTE IMMEDIATE 'truncate table schema2.table1';
end;
/
grant execute on schema1.do_the_truncate to schema1;
grant execute on schema2.do_the_truncate to schema2;
exec do_the_truncate;
Error report:
ORA-01031: insufficient privileges
*Cause: An attempt was made to change the current username or password
without the appropriate privilege. This error also occurs if
attempting to install a database without the necessary operating
system privileges.
When Trusted Oracle is configure in DBMS MAC, this error may occur
if the user was granted the necessary privilege at a higher label
than the current login.
Privilege issue
swamy, July 18, 2014 - 9:02 am UTC
Hi Tom,
Even after granting Privilege on proc to user, I am still facing issue, find my code below and please advise.
create or replace procedure schema1.do_the_truncate
is
begin
EXECUTE IMMEDIATE 'truncate table schema2.table1';
end;
/
grant execute on schema1.do_the_truncate to schema1;
grant execute on schema2.do_the_truncate to schema2;
exec do_the_truncate;
Error report:
ORA-01031: insufficient privileges
*Cause: An attempt was made to change the current username or password
without the appropriate privilege. This error also occurs if
attempting to install a database without the necessary operating
system privileges.
When Trusted Oracle is configure in DBMS MAC, this error may occur
if the user was granted the necessary privilege at a higher label
than the current login.
response to swamy
Parag S, August 22, 2019 - 12:07 pm UTC
Your procedure needs to be in the same schema as the table.
August 22, 2019 - 2:25 pm UTC
Thanks... but I doubt swamy's been waiting the past 5 years for an answer ;)