Dynamic Table Grants

[msacks]

Hi Everyone-

I'm hoping someone can give me some guidance on a project I'm working on. The project is pretty simple, we replicate tables from our payroll vendor into our Data Warehouse database so we can perform further ETL on the data. Typically we drop the tables and re-create them every day. We don't want any other development teams to start using the tables until we've replicated all of them (about 20 in total). Once all the tables are replicated, we want to grant SELECT permissions to the tables all at once. I created an Oracle stage that dynamically creates a grant statement for every table and saves the results in a text file (see below)

SELECT 'GRANT SELECT ON ' || table_name || ' TO SELECT_USERS'
FROM user_tables

Now all I need is a way to read this file and execute all the grant statements. I tried creating a new job with an Oracle stage and setting the query type to "Load SQL from a file at run time" and putting the path the file in the in the SQL Statement box. When I run the job it fails and tells me the SQL Statement was invaild. Here's an example of the SQL Statement: "GRANT SELECT ON TABLE_XXX TO SELECT_USERS". I can run this statement via SQL*Plus, so I know syntactically it's valid. I tried changing the stage to "Enter custom SQL statement" and just hard-coding a single GRANT statement in the SQL, but I still get invalid SQL statement. Hopefully, someone can tell me how to accomplish this. Thanks in advance!!

[sud]

Well, it is complaining about the fact that there is a URL and not an SQL statement as it expects. It is not complaining about the SQL inside the file since it has not accessed it at all. This is not how it works, that you just put the URL to an SQL file and it will execute it.

What you can do is, put the SQL statements within a procedure and invoke the procedure from the oracle stage in the open command and write a dummy SQL in the actual SQL statement for the oracle stage like: select 1 from dummy; or something.

Only select statements are allowed in the oracle stage, so it won't allow grant statements.

[chulett]

Or you could, after job, start a scripted sqlplus session and run that file from inside sqlplus.

[sud]

Or you could, after job, start a scripted sqlplus session and run that file from inside sqlplus.

Very true. And, in the lines of what Craig is suggesting, have worked on project where we were doing very similar stuff and we created a complete oracle package for the replication of tables. The whole work related to the database replication can be handled from Oracle side and may be you would just want to trigger related package functions from datastage job sequence or before/after subroutines which can be done using the strategy that Craig suggested.