This content has been marked as final. Show 16 replies
Unless schema A does a grant to B ' ... with admin option;' then B can then grant those rights to other user(s). The tables belong to a user, someone has to own the objects. The owner can grant/revoke rights on his/her objects as appropriate for the other database users.
B apparently cannot grant permissions on objects in schema A ...
Right, DBA role has quite a bit of power. If the security setup requires granting anyone that role, its time to rethink the design and privilege grants.
DBA role (not ideal I know)!
Welcome to the forum!
It is funny that B can create the tables, but can't grant privileges on them.
Why can't A grant the privileges (or create the table)?
Be careful about granting privileges like SELECT ANY TABLE to C. That will give C the power to use any table in any schema, including schemas and tables not yet created.
You can write a stored procedure (in schema A) that grants the specific privileges you need. Use EXECUTE IMMEDIATE in the stored procedure to issue the GRANT commands.
user10714848 wrote:Well, I'm replying to pose another question. Could this solution be a correct approach?
Hello, I've got an amusing problem -- we have a large table that requires a batch process to do millions of updates, and it needs to finish faster. This takes several hours as an update, but a create table as select does the same thing in about a minute. Yay! Then we just drop the old table and rename the new table to the old, and rebuild all the index, and grant select/insert/update/delete permissions to another user that needs access..
Creating a new table, drop and rename instead of update the original table?
I personally prefer to avoid dropping a table in a production environment. Too many impacts (recompiling packages, procedure, validating constraints, etc.).
But I'm waiting for some expert opinion about this approach.
Thanks for the responses, but unfortunately everything has to be done by user B so having user A grant permissions to B isn't an option.
Sorry, I probably didn't make this clear enough -- this job runs in batch mode on a regular basis. The table owned by user A is dropped and recreated by user B with the CTAS. User A is not set up to run in batch and setting user A up in batch is what we're trying to avoid if at all possible (currently we have hundreds of batch processes and they all run under User B).
Fortunately B and C are both batch userids (no actual person can access them) so probably we'll end up going with "any table" if there's no way to do this.
I researched this and apparently CTAS is considered best practice when you are doing millions of updates. The difference in run time is staggering -- five hours versus one minute.
Our process needs to finish faster, as it prevents users from using the application. It used to run on weekends where this wasn't a problem, but recent changes have resulted in it needing to run on weekdays.
To Frank's earlier point, are you sure that A can't own a procedure that does the grants and that is called by B? That would be the simplest and cleanest approach (of course, the easiest and cleanest way to recreate the table would also be for A to own a stored procedure that did it and have B call the stored procedure-- otherwise, B would need the CREATE ANY TABLE privilege which should virtually never be granted). It sounds like you're saying that's not an option but it's not clear from your explanation why that's not an option-- it doesn't seem like it requires A to be "set up in batch" whatever that means to you.
Tables and other objects in the data dictionary should not be dropped and created in production on a regular basis.
The batch job should TRUNCATE the table and then do INSERT /*+ APPEND */. This is basically equivalent to DROP + CTAS except that the objects and their grants remain.
A user that actually executes in production should not have such powerful privileges; there is a security danger to the system if the user is compromised.
Now, there is another problem in that a TRUNCATE requires DROP privileges on the table ! This is unfortunate, but that's the way Oracle works.
It would be more secure to create a stored procedure in User A that accepts to truncate User A tables. Then you grant user B the privilege to execute the stored procedure.
Finally, one way or another you have to deal with indexes and constraints. How are you doing that right now?
If you read the link you gave down farther, you will see references to TRUNCATE + INSERT /*+ APPEND */ as being equivalent to DROP + CTAS.
Tom Kyte also insists on not dropping and creating tables dynamically in production. See
"Doing DDL in a stored procedure is not a good idea at all."
So go for TRUNCATE + INSERT /*+ APPEND */
Yes. By default, stored procedures are definer's rights stored procedures. If A owns a stored procedure, that stored procedure can do whatever A has been granted privileges to do directly. If A grants B EXECUTE privileges on that stored procedure, when B invokes the stored procedure, the procedure runs with A's privileges.
I'd echo Stew's point, though, that re-creating objects in Oracle is generally a bad idea and that a TRUNCATE with a direct-path insert, possibly combined with disabling and rebuilding the indexes would be more appropriate than a CTAS. If you're going to stick with the CTAS, however, that really needs to be done in a stored procedure owned by A so that B doesn't need CREATE ANY TABLE.
Stew -- I just rebuild the indexes and constraints after the CTAS. That takes about 15 minutes, but is still much faster than the 5 hour update.
TRUNCATE + INSERT might be worth a shot, but the source for the data is another system so I'd first have to copy the table somewhere else, and I suspect this whole operation is much slower than CTAS since CTAS is DDL. But I'll try it and see, it would certainly be better not to have to drop tables and rebuild everything.
The setup I inherited is a bit of a mess already, way too many users seem to have DBA role for some reason.
user10714848 wrote:That's more or less how it works.
Justin -- maybe I'm misunderstanding, are you saying User B inherits the grant permissions of User A when running User A's package, and thus would be able to grant the permissions to C that way? That would be great, if true!
More precisely, if A owns a procedure, created with AUTHID DEFINER (which is the default), then anything done inside that procedure is done with A's privileges, even if B calls it. No matter what the procedure does, the only privilege B needs is the EXECUTE privilege on the procedure.
A will have to log on once (either to create the procedure, or after the procedure is created) to grant the EXECUTE privilege to B, but after that, A will not need to log on any more. The procedure, and the privileges on the procedure, will not go away when any tables are dropped.