Thursday, March 05, 2009

Automatically Granting Privileges on Newly Created Tables

I saw an interesting question on the Oracle-L forum: We have PeopleSoft applications that create tables on the fly. Developers want access to those tables that will be created on the fly, in case the process that creates it ends abnormally. I looked into granting via DDL triggers, it seemed like granting access via them is a problem. Is there a way to grant access other than doing 'grant select any'.

I am finding it increasingly common for developers and support staff not to have direct access to the PeopleSoft OwnerID schema (SYSADM) in even development environments, but using personal database logins. They need to have SELECT privilege on tables. The problem described above also occurs when table is rebuilt by Application Designer. When it is dropped the granted privileges disappear with the table.

It is certainly true that you cannot issue DDL in DDL trigger on the same object that caused the trigger to fire. You will get an error caused by a deadlock in the recursive SQL.

CREATE OR REPLACE TRIGGER gfc_grant AFTER CREATE ON sysadm.schema
DECLARE
l_cmd VARCHAR2(1000 CHAR);
BEGIN
IF ora_dict_obj_type = 'TABLE' THEN
l_cmd := 'GRANT SELECT ON '||ora_dict_obj_owner||'.'||ora_dict_obj_name||' TO gofaster';
dbms_output.put_line('DDL:'||l_cmd);
EXECUTE IMMEDIATE l_cmd;
END IF;
END;
/
show errors
set serveroutput on
DDL:GRANT SELECT ON SYSADM.T TO gofaster
CREATE TABLE t (a 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 9

Some sites have regular maintenance jobs that recreate any missing privileges.

However, there is a way to have the privileges automatically recreated soon after the table is built. You could use a DDL trigger to submit a job to the Oracle job scheduler to grant the privileges. You can't submit DDL directly via the job scheduler, so you need a procedure to which you can pass the DDL as a string parameter, and then execute it as dynamic SQL in the procedure.

CREATE OR REPLACE PROCEDURE myddl
(p_ddl IN VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE p_ddl;
END;
/

Then the DDL trigger can submit a job to call this procedure with the GRANT command in the parameter.

CREATE OR REPLACE TRIGGER gfc_grant
AFTER CREATE ON sysadm.schema
DECLARE
l_jobno NUMBER;
BEGIN
IF ora_dict_obj_type = 'TABLE' THEN
dbms_job.submit(l_jobno,'myddl(''GRANT SELECT ON '||ora_dict_obj_owner||'.'||ora_dict_obj_name||' TO gofaster'');');
END IF;
END;
/

If I create this table

CREATE TABLE t (a NUMBER);

I get this job

SELECT * FROM dba_jobs
/

JOB LOG_USER
---------- ------------------------------------------------------------------------------------------
PRIV_USER
------------------------------------------------------------------------------------------
SCHEMA_USER                                                                                LAST_DATE
------------------------------------------------------------------------------------------ -------------------
LAST_SEC                 THIS_DATE           THIS_SEC                 NEXT_DATE
------------------------ ------------------- ------------------------ -------------------
NEXT_SEC                 TOTAL_TIME BRO
------------------------ ---------- ---
INTERVAL
---------------------------------------------------------------------------------------------------------------
FAILURES
----------
WHAT
---------------------------------------------------------------------------------------------------------------
60 SYSADM
SYSADM
SYSADM
19:04:52 05/03/2009
19:04:52                          0 N
null

myddl('GRANT SELECT ON SYSADM.T TO gofaster');


After the job has run, which should normally only be a few seconds, I get these privileges

SELECT * FROM user_tab_privs WHERE table_name = 'T';

GRANTEE
------------------------------------------------------------------------------------------
OWNER
------------------------------------------------------------------------------------------
TABLE_NAME
------------------------------------------------------------------------------------------
GRANTOR
------------------------------------------------------------------------------------------
PRIVILEGE
---------------------------------------------------------------------------------------------------------------
GOFASTER
SYSADM
T
SYSADM
SELECT


Added 2.4.2009: Following this posting it was put to me that you could get Application Designer to build scripts with the commands to add the privilege by adding a second command to the create table DDL model. See part 2.

7 comments :

Noons said...

The wya I handle this is slightly different:
1- create a role with the required select grants.
2- create an "access user" that everyone connects to if they want to see the tables.
3- grant the role to the access user.
4- have a login trigger on the access user that does a "alter session set current_schema=sysman/psman".

And that's it. If I need to change access rules, I change the role. And with the current_schema, no one needs to type owner.table_name and/or create synonyms.

Need to stop people accessing the system? Turn off the role and/or disable the access user id.

Nice and easy.

David Kurtz said...

I didn't make it clear in the original posting, but I was granting the privilege to a role called gofaster. The grant command would have been exactly the same if I was granting the privilege to a user called gofaster.

Ricardo Andres said...

Hola la respuesta es un poco tarde, expongo mi caso el cual casi no me dejó descansar todo un día buscando solución al error:
ORACLE - ORA-00604: error occurred at recursive SQL level 1
ORA-30511: invalid DDL operation in system triggers

En mi caso se debía a un trigger creado en mi esquema de producción el cual auditaba las siguientes acciones sobre dicho esquema:
BEFORE CREATE OR ALTER OR DROP ON SCHEMA

Hice la siguiente consulta para detectarlo, sobre mi esquema de producción:

SELECT *
FROM USER_SOURCE
WHERE UPPER(TEXT) LIKE '%CREATE OR ALTER%';

Este me dio el nombre del script que me estaba causando los problemas, lo deshabilité y listo, una vez realizadas las actividades necesarias volví y lo activé.

Espero les ayude…

David Kurtz said...

Translation into english of Ricardo's comment:

Hi the answer is a little late, I a whole day looking for solution to this error:
ORACLE - ORA-00604: error occurred at recursive SQL level 1
ORA-30511: invalid DDL operation in system triggers

In my case it was due to a trigger created in my production schedule which was auditing the following actions on this scheme:
CREATE OR ALTER OR BEFORE DROP ON SCHEMA

I used the following query to detect it on my production schedule:

SELECT *
FROM user_source
WHERE UPPER (TEXT) LIKE '% CREATE OR ALTER%';

This gave me the name of the script that was causing me problems, so I disabled it after completing the necessary activities turned and activated it.

Hope it will help ...

Suhas K P said...

@David Kurtz. Thanks a ton :)

Techie and blogger said...

Great Post David

I've tested and it works but there's no history of it running in dba_jobs or user_jobs. Am I missing something obvious?

David Kurtz said...

Matt, glad to hear it works. There is no log of successful jobs with DBMS_JOB.