Script to Grant select privilege to other user on objects
Script to Grant Select Privileges on tables, views, Sequence belong to other user by following scripts:
1. Login with the user which grant permission to other user
2. Execute the following script from that user login
3. Example grant Select privileges to someone parameter.
FOR x IN (SELECT * FROM user_tables)
EXECUTE IMMEDIATE 'GRANT SELECT ON ' || x.table_name || ' TO <>';
SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF
SELECT 'GRANT SELECT ON "' || u.object_name || '" TO &1;'
FROM user_objects u
WHERE u.object_type IN ('TABLE','VIEW','SEQUENCE')
AND NOT EXISTS (SELECT '1'
FROM all_tab_privs a
WHERE a.grantee = UPPER('&1')
AND a.privilege = 'SELECT'
AND a.table_name = u.object_name);
You got the spool file in above statement then you execute the spool file on SQLPLUS command prompt:
SET PAGESIZE 14
SET FEEDBACK ON
SET VERIFY ON
Verify the priviliges Scott user having:
select * from dba_ROLE_PRIVS where Grantee =’SCOTT’;
select * from dba_TAB_PRIVS where Grantee = ‘SCOTT’;
select * from dba_SYS_PRIVS where GRANTEE = ‘SCOTT’;