Grant All Tables at Object Level
For those who don't like the old username and want to switch all data to a well-named user, you may consider to rename username. It might be the solution you want to adopt.
Sometimes, you may want a user who can select all the tables from the other user's. But the question is how to do the granting operation in a second? Is there any SQL syntax can match our expectation? or should we grant the tables one by one?
The truth is there is no such syntax that can grant all tables owned by others to grantee, but I provide an alternative that can compose all necessary statements and save as a SQL file for execution:
- Determine who is the owner (or grantor) and who is the grantee.
- Edit select.sql, the content is as below, please replace OWNERNAME and GRANTEENAME in the statement for your need:
- In Unix or Linux, execute the following command with a user who can query DBA_* data dictionary above. For convenience, we choose SYSTEM. The composed SQL statements will go to grant.sql:
- Execute grant.sql with the owner or has GRANT ANY OBJECT PRIVILEGE user, in this case, we use SYSTEM
SET HEAD OFF; --TURN OFF THE COLUMN HEAD.
SET FEEDBACK OFF; --TURN OFF THE "ROW SELECTED" FEEDBACK.
--COMPOSE EXECUTABLE SQL STATEMENTS BELOW:
SELECT 'GRANT SELECT ON OWNERNAME.'||OBJECT_NAME||' TO GRANTEENAME;' "SQL STATEMENTS" FROM DBA_OBJECTS WHERE OWNER='OWNERNAME' AND OBJECT_TYPE='TABLE';
EXIT;
$ sqlplus -S system/password@tnsname @select.sql > grant.sql
The content of grant.sql will be like this:GRANT SELECT ON OWNERNAME.TABLE1 TO GRANTEENAME;
GRANT SELECT ON OWNERNAME.TABLE2 TO GRANTEENAME;
GRANT SELECT ON OWNERNAME.TABLE3 TO GRANTEENAME;
GRANT SELECT ON OWNERNAME.TABLE4 TO GRANTEENAME;
...
$ sqlplus system/password@tnsname @grant.sql
Then, you can see a lot of Grant succeeded. in the console. We are done.
If you want grant execute on stored procedure, just replace the OBJECT_TYPE in step 2 from TABLE to PROCEDURE, FUNCTION or PACKAGE. That's why I choose to query DBA_OBJECTS dictionary view rather than DBA_TABLES.
Grant SELECT ANY TABLE at System Level
Another way with higher security risk is to grant SELECT ANY TABLE system privilege to grantee, it lets grantee has the power to select all tables in database, this is not what we want. But, if you know where the risk is and make sure the grantee is very trustable, you can solve grantee's problem in a statement. Furthermore, after the grantee has done his work, you can revoke the privilege from the grantee, this is how we do the trick in real world.
In one case, DBA may choose a system privilege SELECT ANY TABLE for his solution, that is, an local account is dedicated for a private database link of a remote database, and only very limited remote users can use the database link to do routine operations, and most important, there is no one knows the special account's password except DBA.
After you grant the object privileges to grantee, you can check the results by the methods in this post:
How to Check Table is Granted to You