| Schema level event triggers [message #59507] |
Thu, 27 November 2003 18:52  |
Mark Sonter
Messages: 2
Registered: November 2003
|
Junior Member |
|
|
All,
Have a requirement to update roles with privileges based on a create/drop of tables/views in different schemas. Has anyone worked with schema level triggers for this and could you provide some guidelines.
thanks in advance
Mark
|
|
|
|
| Re: Schema level event triggers [message #59509 is a reply to message #59507] |
Thu, 27 November 2003 23:53  |
Djay
Messages: 2
Registered: November 2003
|
Junior Member |
|
|
Hi,
You can do it. However please note some inherent issues with it.
a) Since GRANT issues a commit, you cannot do the commit inside the trigger.
b) You would need to use DBMS_SQL package to grant the privileges on the objects.
The logic flows something like this.
If for example, anytime a table is created by user SCOTT.. if SELECT, INSERT, UPDATE and DELETE privileges need to be granted to the role ROL.
Create a trigger that fires after the CREATE statement on a TABLE type object which inserts the name of the created table into the table created for this purpose
Call a procedure PROC1 for submitting a job that calls a procedure PROC2 that grants the privileges to role ROL. This is to circumvent the problem of trigger not being able to commit.
The commit is done within the procedure PROC2 called by the DBMS_JOB package and therefore the trigger needs to execute autonomously.
PROC1 ---> Submits the job
PROC2 ---> Retrieves the table name and grants the privileges on the table to role ROL using DBMS_SQL package.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
