Dynamic Table Grants
Posted: Fri Apr 18, 2008 11:19 am
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!!
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!!