Script to Grant select privilege to other user in oracle

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:

First way:
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.

Begin
FOR x IN (SELECT * FROM user_tables)
LOOP
EXECUTE IMMEDIATE 'GRANT SELECT ON ' || x.table_name || ' TO <>';
END LOOP;
end;
/

Second way:

SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF
SPOOL temp.sql
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);
SPOOL OFF

You got the spool file in above statement then you execute the spool file on SQLPLUS command prompt:

@temp.sql
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’;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.