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.
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.
Then the DDL trigger can submit a job to call this procedure with the GRANT command in the parameter.
If I create this table
I get this job
After the job has run, which should normally only be a few seconds, I get these privileges
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.