Skip to Main Content
  • Questions
  • Workaround for "Select Any Table" privilege

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Saibabu.

Asked: August 25, 2000 - 5:44 pm UTC

Last updated: May 07, 2012 - 10:41 am UTC

Version: 8.1.6

Viewed 10K+ times! This question is

You Asked

Hi,We have a requirement that one user called AMEXMART should be able to select the all tables being added to another user NET without granting eachtime the table is created.We don't want to give "select any table" system privilege to AMEXMART user.Is there any workaround for this.
Thanks
Saibabu

and Tom said...



Since you have 8.1.6, you can use an ON CREATE trigger to do this perhaps.

Something like:

create or replace trigger do_grant
after CREATE on schema
declare
l_str varchar2(255);
l_job number;
begin
if ( ora_dict_obj_type = 'TABLE' )
then
l_str := 'execute immediate "grant select on ' ||
ora_dict_obj_name ||
' to scott";';
dbms_job.submit( l_job, replace(l_str,'"','''') );
end if;
end;
/



That, when run in a given user account will schedule a job to grant SELECT on any table created to SCOTT. We use dbms_job to run the grant in the background because this is executing as part of a larger data dictionary transaction and we cannot commit (nor can we use an autonomous transaction - the table is not yet "fully" created)...

If you have job_queue_processes set to >0 and job_queue_interval set to some reasonable number (I use 60 for 1 minute intervals), then scott will have access to any table about 1 minute AFTER its created....


Does that work for you?


Rating

  (16 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

trigger to grant selects

Hubertus Krogmann, August 29, 2001 - 7:35 am UTC

Hi

this was what I searched, even with a explanation why not grant directly.

Thanx!

Anandhi, March 27, 2003 - 1:10 pm UTC

I used your code:

create or replace trigger do_grant
after CREATE on m_user.schema
declare
l_str varchar2(255);
l_job number;
begin
if ( ora_dict_obj_type = 'TABLE' )
then
l_str := 'execute immediate "grant select on ' ||
ora_dict_obj_name ||
' to g_user";';
dbms_job.submit( l_job, replace(l_str,'"','''') );
end if;
end;
/

I had to create this trigger as system user becos when I try to run it as m_user, I get an insufficient privileges error.
The trigger compiles well, but when I try to create a table as m_user, I get this error:

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-30511: invalid DDL operation in system triggers
ORA-06512: at line 7

I am probably missing something here - what is that?

Thanks for your time.

Anandhi



Tom Kyte
March 27, 2003 - 2:12 pm UTC

well, you know -- you should not use SYS or SYSTEM for *anything* remotely likely this.

They are our accounts.  You should however grant your account the privileges necessary to perform its job.


But -- that aside, you'll have to be creative and debug this a little yourself -- or at least give me a 100% test case with which to reproduce -- including nice things like database version and OS....

Here is my test case showing "this works"

ops$tkyte@ORA817DEV> drop user m_user cascade;
User dropped.

ops$tkyte@ORA817DEV> drop user g_user cascade;
User dropped.

ops$tkyte@ORA817DEV> create user m_user identified by m_user;
User created.

ops$tkyte@ORA817DEV> create user g_user identified by g_user;
User created.

ops$tkyte@ORA817DEV> grant connect, resource to m_user;
Grant succeeded.

ops$tkyte@ORA817DEV> grant connect to m_user;
Grant succeeded.

ops$tkyte@ORA817DEV>  create or replace trigger M_USER.do_grant
  2    after CREATE on m_user.schema
  3    declare
  4    l_str varchar2(255);
  5    l_job number;
  6    begin
  7    if ( ora_dict_obj_type = 'TABLE' )
  8    then
  9    l_str := 'execute immediate "grant select on ' ||
 10    ora_dict_obj_name ||
 11    ' to g_user";';
 12    dbms_job.submit( l_job, replace(l_str,'"','''') );
 13    end if;
 14    end;
 15  /
Trigger created.

ops$tkyte@ORA817DEV> @connect m_user/m_user

m_user@ORA817DEV> create table t ( x int );
Table created.

m_user@ORA817DEV> select job, what from user_jobs;

       JOB WHAT
---------- ------------------------------
       289 execute immediate 'grant
           select on T to g_user';

<b>wait a bit...</b>

m_user@ORA817DEV> /
no rows selected


Job run, grant done.
 

Wouldn't the grant run under the schema from which the trigger fires

NC, March 31, 2003 - 8:31 am UTC

Wouldn't in this case the job get submitted under the owner of the trigger ie ops$tkyte and therefore the grant fail ?

Tom Kyte
March 31, 2003 - 9:23 am UTC

good eye, I missed a schema name on there. corrected...

Security issues

A reader, April 15, 2005 - 2:44 pm UTC

This works great.. Was wondering if there are any security issues associated with automatically granting privileges like this, especially if one of the schema owners has DBA privileges.

Tom Kyte
April 15, 2005 - 2:56 pm UTC

absolutely, you are granting access automatically, so people won't "see" it and might not know it happened. so directly, not really -- but indirectly because it doesn't work as "normal" it could have side effects.

virgile, August 11, 2005 - 11:24 am UTC

It's a very good trick. Using the dbms_job is simple and it works well.
I was trying to do it using another way (using DML, autonomous transaction, etc.. but it generates many errors like ORA-04020 or ORA-06519).

Thanks again.

Virgile


Automating Grant Operations

antonio, January 26, 2006 - 9:59 am UTC

I have read your comment about the Metalink note 210693.1 "How to Automate Grant Operations When New Objects Are Created in a SCHEMA/DATABASE", in which you suggest the use of the following trigger:

create or replace trigger do_grant
after CREATE on m_user.schema
declare
l_str varchar2(255);
l_job number;
begin
if ( ora_dict_obj_type = 'TABLE' )
then
l_str := 'execute immediate "grant select on ' ||
ora_dict_obj_name ||
' to g_user";';
dbms_job.submit( l_job, replace(l_str,'"','''') );
end if;
end;
/

We have used it, substituting the old Metalink procedure with the trigger in EACH schema, but i have got many of the following errors:
ORA-12012: error on auto execute of job nnnn
ORA-00942: table or view does not exist
One of the reasons is because we have many temporary tables that are soon dropped.
Using the execute immediate directly in the dbms_job, the command will stay in the job queue waking up and signaling the error in the alert and trace if something goes wrong.
With the more complex Metalink method, we didn't get errors because the dbms_job runs the procedure.
What do you think about it?
And is there any cons using the autonomous transaction in this case?
My users signal me locks and disconnections on the client side.

Thanks in advance for you help

Tom Kyte
January 26, 2006 - 10:37 am UTC

their more complex one could still have this problem as your job might run, opens the query, then you drop the table, and then the job tries to grant.

But anyway - this would be solved in my trigger simply by modifying the block submitted to catch the ora-942 error and ignoring it. You know, error handling to ignore "errors" that are not errors.


seems that if you drop+create lots though, you have a bad bug in your design.


perhaps you should use a naming convention to skip the temporary tables granting all together.

DDL Trigger when create table is executed by different user

Rory Concepcion, April 10, 2007 - 11:03 pm UTC

Hi Tom,

Your example was great.
I did the following.

create role test_access;
create user test identified by test;
create user monitor identified by monitor;
grant connect, resource to test;
grant connect, resource to monitor;

connect test/test
create or replace trigger test_schema_trig
before CREATE on test.schema
declare
l_str varchar2(255);
l_job number;
begin
if ( ora_dict_obj_type = 'TABLE' )
then
l_str := 'execute immediate "grant select,insert,update,delete on '||ora_dict_obj_owner||'.'||ora_dict_obj_name ||' to test_access";';
dbms_job.submit( l_job, replace(l_str,'"','''') );
end if;
end;
/

Trigger created.

connect monitor/monitor

create table test.t1
(name varchar2(30));

The problem is, the trigger doesn't seem to execute when the table is created by monitor user.

What I want is to "grant select, insert,update,delete on table_name to test_access" whenever a table is created.
But the tables are often times created by monitor user instead of test user.

Thanks
Tom Kyte
April 11, 2007 - 11:07 am UTC

http://docs.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7004.htm#sthref7985

...
SCHEMA

Specify SCHEMA to define the trigger on the current schema. The trigger fires whenever any user connected as schema initiates the triggering event.
......

since monitor is not connected as the schema....


here is an approach.

Use a database level trigger - here I am using a "privileged account" to hold this database trigger (so as to not have to give TEST that capability).



ops$tkyte%ORA10GR2> create user test identified by test default tablespace users quota unlimited on users;

User created.

ops$tkyte%ORA10GR2> create user monitor identified by monitor default tablespace users quota unlimited on users;

User created.

ops$tkyte%ORA10GR2> grant create session, create table, create procedure to test;

Grant succeeded.

ops$tkyte%ORA10GR2> grant create session, create ANY table to monitor;

Grant succeeded.

ops$tkyte%ORA10GR2> create role test_access;

Role created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> connect test/test
Connected.
test%ORA10GR2>
test%ORA10GR2> create table job_parm_table( job number primary key, tname varchar2(30) ) organization index;

Table created.

test%ORA10GR2> create or replace procedure do_grant( p_job in number )
  2  as
  3          l_rec job_parm_table%rowtype;
  4  begin
  5          select * into l_rec from job_parm_table where job = p_job;
  6          execute immediate 'grant select, insert, update, delete on ' || l_rec.tname || ' to test_access';
  7          delete from job_parm_table where job = p_job;
  8  end;
  9  /

Procedure created.

test%ORA10GR2> grant insert on job_parm_table to ops$tkyte;

Grant succeeded.

test%ORA10GR2> grant execute on do_grant to ops$tkyte;

Grant succeeded.

test%ORA10GR2>
test%ORA10GR2> connect /
Connected.
ops$tkyte%ORA10GR2> create table msg ( txt varchar2(255) );

Table created.

ops$tkyte%ORA10GR2> create or replace trigger test_schema_trig
  2  before CREATE on database
  3  declare
  4    l_str varchar2(255);
  5    l_job number;
  6  begin
  7      if ( ora_dict_obj_type = 'TABLE' and ora_dict_obj_owner = 'TEST' )
  8        then
  9          dbms_job.submit( l_job, 'test.do_grant(JOB);' );
 10                  insert into test.job_parm_table( job, tname ) values ( l_job, ora_dict_obj_name );
 11      end if;
 12  end;
 13  /

Trigger created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> connect test/test
Connected.
test%ORA10GR2> create table t1( x int );

Table created.

test%ORA10GR2>
test%ORA10GR2> connect monitor/monitor
Connected.
monitor%ORA10GR2> create table test.t2(name varchar2(30));

Table created.

monitor%ORA10GR2>
monitor%ORA10GR2> connect /
Connected.
ops$tkyte%ORA10GR2> select grantee, privilege from dba_tab_privs where owner = 'TEST';

GRANTEE                        PRIVILEGE
------------------------------ ----------------------------------------
OPS$TKYTE                      EXECUTE
OPS$TKYTE                      INSERT

ops$tkyte%ORA10GR2> select * from test.job_parm_table;

       JOB TNAME
---------- ------------------------------
        36 T1
        37 T2

ops$tkyte%ORA10GR2> pause

ops$tkyte%ORA10GR2> select grantee, privilege from dba_tab_privs where owner = 'TEST';

GRANTEE                        PRIVILEGE
------------------------------ ----------------------------------------
OPS$TKYTE                      EXECUTE
TEST_ACCESS                    DELETE
OPS$TKYTE                      INSERT
TEST_ACCESS                    INSERT
TEST_ACCESS                    SELECT
TEST_ACCESS                    UPDATE
TEST_ACCESS                    DELETE
TEST_ACCESS                    INSERT
TEST_ACCESS                    SELECT
TEST_ACCESS                    UPDATE

10 rows selected.


DDL Trigger

Rory Concepcion, April 11, 2007 - 4:18 pm UTC

Wow!!!

Thanks a lot Tom.
Your answer would be of great use to us.

granting select role on table which ever table is create in any schema

vinodh, March 24, 2011 - 6:02 am UTC

Hi, 

i have created a trigger to grant a select role to the owner of the user and create a synonym for that table can any one help from the below code what i have written.

CREATE OR REPLACE TRIGGER ddl_create_alter
  2     AFTER CREATE or ALTER ON DATABASE
  3     DECLARE
  4             sql_stmnt varchar2(500);
  5             v_job   number;
  6     BEGIN
  7     IF ora_dict_obj_type='TABLE' THEN
  8                  sql_stmnt :='grant select on ' || ora_dict_obj_name ||' to '||ora_dict_obj_owner;
  9                  EXECUTE IMMEDIATE sql_stmnt;
 10                  sql_stmnt:='create or replace public  synonym  synonym_'||ora_dict_obj_name||' for '||ora_dict_obj_owner||'.'||ora_dict_obj_name;
 11      END IF;
 12
 13     EXECUTE IMMEDIATE sql_stmnt;
 14     END;
 15  /

Trigger created.

SQL> create table t(v varchar2(2));
create table t(v varchar2(2))
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-30511: invalid DDL operation in system triggers
ORA-06512: at line 7


  

Tom Kyte
March 24, 2011 - 8:32 am UTC

the table isn't fully created yet, it doesn't exist in a state for you to grant on. Additionally - GRANT commits, you cannot commit in a trigger. And before you run off and say "I'll use an autonomous transaction" - well, the create hasn't finished yet, so if you use an autonomous transaction - the GRANT again will not see it.


If you read the original answer, this was all sort of explained???

A reader, March 24, 2011 - 10:41 pm UTC

Hi Tom,

Can we use dbms_schedular for this?

Thanks
Tom Kyte
March 25, 2011 - 10:56 am UTC

sure

performing DDL operation() in DDL triggers .

vinodh, March 24, 2011 - 11:29 pm UTC

can any one help me how to create a DDL trigger to grant select on table to owner of that table and creating the synonym on that table

30511, "invalid DDL operation in system triggers"
code :
CREATE OR REPLACE TRIGGER ddl_create_alter
AFTER CREATE or ALTER ON DATABASE
DECLARE
sql_stmnt varchar2(500);
stmt integer;
dummy integer;
BEGIN
IF ora_dict_obj_type='TABLE' THEN
sql_stmnt:='create or replace public synonym synonym_'||ora_dict_obj_name||' for '||ora_dict_obj_owner||'.'||ora_dict_obj_name;
stmt := dbms_sql.open_cursor;
dbms_sql.parse(stmt, sql_stmnt,dbms_sql.v7);
dummy := dbms_sql.execute(stmt);
dbms_sql.close_cursor(stmt);
END IF;
END;
/

Tom Kyte
March 25, 2011 - 10:59 am UTC

oh my gosh.

did you not READ the original answer?????

for goodness sake, you were already

a) told why this won't work in theory - even if you could do DDL in the trigger
b) told why DDL cannot be done in a trigger without using an autonomous transaction
c) told why using an autonomous transaction in fact cannot be used

d) READ THE ORIGINAL ANSWER which tells you how to accomplish this dubious task.

@vinodh

Michel Cadot, March 25, 2011 - 7:56 am UTC


can any one help me how to create a DDL trigger to grant

This is in the second answer of this thread, please read it.

to grant select on table to owner of that table

This is useless, the owner has this privilege as any other ones on his table.

Regards
Michel

Creating Synonyms

Ali, July 12, 2011 - 5:50 pm UTC

Hey Tom,

Your example is exactly what I need to grant access, but I'm running into issues when trying to create a public synonym.

Using the same code excpet,
l_str := 'execute immediate "create or replace public synonym ' || ora_dict_obj_name || ' for ' || ora_dict_obj_name || '";';

I get an insufficient privileges error. The user id has rights to create public synonyms; I can grab the statement from dba_jobs and create the synonym using the same user id. Are there priviliges preventing me from running dbms_job? I wouldn't think there are since I'm able to run the grant statement. Maybe I'm missing something about public synonyms?

Thanks for the help,
Ali
Tom Kyte
July 13, 2011 - 8:05 pm UTC

I'm just going to say that this trigger SCARES me to death and should not exist.


You do NOT want to create or replace a public synonym like that, you do not even want to use public synonyms.

What if I created a table called ALL_USERS.
What if I created a table called ..... and so on....

Man o man, this is a recipe for utter *disaster*.


I'm afraid to tell you how to fix this, since I consider this a completely and totally bad idea.

Convince me it isn't and I'll tell you what you are doing wrong. Why oh why are you doing this?? It really isn't a good move.

trigger and ddl

A reader, May 04, 2012 - 3:22 pm UTC

create or replace trigger do_audit
  after CREATE on database 
begin
    if ( ora_dict_obj_type = 'USER' )
    then
        execute immediate 'audit create session by '||ora_dict_obj_name;
    end if;
end;


Above works fine in a 11g R2 environment, but I have a few questions;

1) Is it a bad idea DO_AUDIT is owned by SYS ?
2) Is AUDIT a DDL command? If so, although it works, should I still call it with DBMS_JOB? If AUDIT is not a DDL, is that why it is working without DBMS_JOB?

Tom Kyte
May 06, 2012 - 2:45 pm UTC

why don't you just audit create session?


period

no by - just audit create session - it'll get everyone.




I cannot recommend, I will not recommend, in fact I would 'anti-recommend' using SYS for this.


If you do this, you should use a job.

but I don't think you want or need to do this.

audit create session

A reader, May 07, 2012 - 3:02 am UTC

Thanks Tom.

Can't use AUDIT CREATE SESSION, because I want to audit everyone except a few users - sorry, I omitted that requirement in the code for brevity.

I will change it to non-SYS user and use DBMS_JOB. In 11g R2, can't find anywhere in the documentation that DBMS_JOB.SUBMIT doesn't commit? What I did find was "...Oracle recommends that you switch from DBMS_JOB to Oracle Scheduler"..hmmm!
Tom Kyte
May 07, 2012 - 5:26 am UTC

it doesn't commit. did you find anything to say it does?

we don't say that dbms_job doesn't use sqlserver either (but it doesn't)

Privileges for a particular schema to another schema

Galen Boyer, May 07, 2012 - 9:00 am UTC

Hi Tom,

Any chance Oracle might consider or be considering having the "grant
any FOR SCHEMA TO XXX". ie, instead of "grant someaction any
someobjecttype to XXX" a grant of the flavor, "grant someaction any
someobjecttype FOR schemaname to XXX"? I can think of quite a few
ways that would be useful. One would be that a schema could grant
everything it has to another user or another role and solve the
original question. Another would be that a schema could be locked
down, yet we could grant some deployment role to a particular user and
that deployment role does not have to be for the entire instance.
Tom Kyte
May 07, 2012 - 10:41 am UTC

That sort of functionality is in Database Vault.

http://docs.oracle.com/cd/E11882_01/server.112/e23090/cfrealms.htm#CHDFGFJJ

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library