1

I want to do something that's conceptually simple but seems to be a lot more complex in reality.

Basically, whenever a new table is created for a couple of users in our database, I want to grant select permissions to a role. Basically this:

grant select on TABLENAME to READROLE;

So far my trigger looks something like this:

CREATE OR REPLACE TRIGGER osmm_grant_on_creation

AFTER CREATE ON OSMM.SCHEMA

BEGIN

    //grant goes here

END

Problem is, I can't figure out how to join the two together by getting the name of the newly created table and referencing it through the trigger to the grant. Any suggestions? Thanks.

Community
  • 1
  • 1
  • 3
    Any suggestions? Sure: don't do this. Granted permissions are a part of our database configuration. As such they should be scripted and kept in a source control repository, alongside the DDL for creating the table. – APC Feb 13 '12 at 15:47
  • @APC - Why would I not want to do this? I'm not a DBA and have limited Oracle knowledge so I'm afraid your reply makes no sense. Is there another way to automatically grant select permissions to a role for a new table? Because doing it manually is a chore we'd like to avoid. Triggers seemed like an obvious solution but if there's a "better" way, I'm open to it. –  Feb 13 '12 at 17:47
  • Triggers are the only way to automatically grant permissions on objects, on demand, and it's not easy (as Justin's answer shows). It is difficult because it is unusual to need to grant the same permissions on all the tables in a schema to someone else (role or user). It's more common to grant a variety of privileges on a sub-set of tables, often to different accounts. That is hard logic to put in a trigger. The more common approach (other than cut'n'paste coding) is to use metadata to generate scripts. – APC Feb 14 '12 at 14:07
  • I suspect that isn't want you were hoping for. It's a matter of defining "better". "Better" can mean *less typing* but it can also mean *more robust, more flexible, more traceable*. – APC Feb 14 '12 at 14:15
  • @ACL - Thanks for clarifying. Based on our use-case it still seems this is the best way for us. We have 1000+ tables for these two users, all of which need the same permissions. New tables are created in a manner that doesn't allow us to easily grant privs so rather than continue doing it manually, these seems optimum. Thanks for the feedback though. –  Feb 14 '12 at 15:34

2 Answers2

13

It's likely more complex than you're even thinking. The GRANT statement is DDL which means that it issues implicit commits which means that you cannot put it in a trigger directly. Your trigger would need to submit a job which ran in a separate session after the triggering transaction committed which would actually do the grant. And that means that you have to use the older DBMS_JOB package to schedule the job since the more modern DBMS_SCHEDULER also implicitly commits.

Since you shouldn't be creating tables on the fly in Oracle in the first place, the proper place for this sort of grant is in the build scripts that you run to create the table in the first place. Relying on triggers to do things like grants just tends to make it more difficult to do builds properly because running exactly the same script in two different environments may generate two different results because of differences in the trigger.

If you're determined to go down this path, however, you'd probably want something like

A procedure that grants the privilege

CREATE OR REPLACE PROCEDURE grant_select_to_readrole( p_table_name IN VARCHAR2 )
AS
BEGIN
  EXECUTE IMMEDIATE 'grant select on ' || p_table_name || ' to readrole';
END;

And a trigger that submits a job that calls this procedure

CREATE OR REPLACE TRIGGER osmm_grant_on_creation
  AFTER CREATE ON OSMM.SCHEMA
AS
  l_jobno PLS_INTEGER;
BEGIN
  dbms_job.submit( l_jobno,
                   'BEGIN grant_select_to_readrole( ''' || ora_dict_obj_name || ''' ); END;',
                   sysdate + interval '10' second );
END;

If you were to try to issue DDL in the schema-level trigger itself, you'd get an error

SQL> ed
Wrote file afiedt.buf

  1  create or replace trigger after_create_on_scott
  2    after create on schema
  3  declare
  4  begin
  5    execute immediate 'grant select on scott.emp to hr';
  6* end;
SQL> /

Trigger created.

SQL> create table foo( col1 number );
create table foo( col1 number )
*
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 3
Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • 1
    +1 for saying "don't do this" then -1 for showing them ho wto work around the issues. – APC Feb 13 '12 at 15:48
  • 1
    @APC - I have, regrettably, done something like this to deal with a packaged application that would periodically spawn new tables in which case a trigger was a reasonable workaround (given that replacing the system was a non-starter). But it should be recognized as a massive hack. – Justin Cave Feb 13 '12 at 16:27
  • According to the information I've found, some DDL is allowed within schema-level triggers (which is unclear). – Allan Feb 13 '12 at 16:52
  • 1
    @Allan - No. DDL isn't allowed in schema-level triggers. Updated my answer with the sort of error you'd get if you tried. – Justin Cave Feb 13 '12 at 16:57
  • 4
    Fair point Justin. I suppose the purpose of SO is to provide seekers with answers, even if sometimes the amnswers are a loaded pistol. All we can do is warn them they might play their foot off. So, +1 – APC Feb 13 '12 at 17:04
  • @JustinCave: From Oracle: "The DDL allowed inside these triggers is altering, creating, or dropping a table, creating a trigger, and compile operations." This is specifically related to login and logoff triggers, so it may only be those that allow DDL, but it's somewhat unclear. In any case, it is evident the some DDL is allowed by some system triggers, just not this DDL (and perhaps not in this system trigger). – Allan Feb 13 '12 at 17:20
  • @JustinCave - thanks for the reply. I'll assume it works and tick it. If APC comes back with a solution that isn't a "loaded pistol" I may go that route but will leave the tick. Cheers all. –  Feb 13 '12 at 17:55
-1

you probabply need to do something like: 

CREATE OR REPLACE TRIGGER osmm_grant_on_creation

AFTER CREATE ON OSMM.SCHEMA
DECLARE
new_obj_name varchar2(30);
BEGIN
SELECT ora_dict_obj_name
INTO new_obj_name
FROM dual
WHERE ora_dict_obj_type = 'TABLE';

execute immediate 'grant select on ' || new_obj_name || ' to READROLE';
END

but I can't check if it works

A.B.Cade
  • 16,735
  • 1
  • 37
  • 53
  • As written, I believe this would raise an error if the created object is anything other than a table, since it would try to execute `grant select on to READROLE`. – Allan Feb 13 '12 at 16:55