How to Check table privileges granted to any
USER:
SQL>
---connect to user ashwani
SQL> conn
ashwani/ashwani
Connected.
SQL>
--create table
SQL>
create table mobile (brand varchar2(9));
Table
created.
SQL>
insert into mobile values ('SONY');
1 row created.
SQL>
insert into mobile values ('LUMIA');
1 row
created.
SQL> CONN
SYS AS SYSDBA
Enter
password:
Connected.
SQL> CONN
sonu/sonu
Connected.
SQL>
select * from ashwani.mobile;
select *
from ashwani.mobile
*
ERROR at
line 1:
ORA-00942:
table or view does not exist
SQL> SELECT 'GRANT '||PRIVILEGE|| ' ON '
||GRANTOR|| '.' ||TABLE_NAME|| ' TO '
||GRANTEE|| ';' from dba_tab_privs where grantee in ('SONU') ;
no rows
selected
SQL> conn
sys as sysdba
Enter
password:
Connected.
SQL>
grant select on ashwani.mobile to sonu;
Grant
succeeded.
SQL> select privilege,table_name,grantor from
dba_tab_privs where grantee='SONU';
PRIVILEGE TABLE_NAME GRANTOR
--------------------
------------------------------ --------------------
SELECT MOBILE ASHWANI
Or user
below dynamic script:
SQL>
SELECT 'GRANT '||PRIVILEGE|| ' ON ' ||GRANTOR|| '.' ||TABLE_NAME|| ' TO ' ||GRANTEE|| ';' from dba_tab_privs where
grantee in ('SONU') ;
'GRANT'||PRIVILEGE||'ON'||GRANTOR||'.'||TABLE_NAME||'TO'||GRANTEE||';'
--------------------------------------------------------------------------------
GRANT SELECT
ON ASHWANI.MOBILE TO SONU;
SQL> CONN
sonu/sonu
Connected.
SQL>
select * from ashwani.mobile;
BRAND
---------
SONY
LUMIA
Lets
grant few other privilege to sonu:
SQL> conn
sys as sysdba
Enter
password:
Connected.
SQL>
grant update on ashwani.mobile to sonu;
Grant
succeeded.
SQL> select privilege,table_name,grantor from
dba_tab_privs where grantee='SONU';
PRIVILEGE TABLE_NAME GRANTOR
--------------------
------------------------------ --------------------
UPDATE MOBILE ASHWANI
SELECT MOBILE
ASHWANI
SQL>
SELECT 'GRANT '||PRIVILEGE|| ' ON ' ||GRANTOR|| '.' ||TABLE_NAME|| ' TO ' ||GRANTEE|| ';' from dba_tab_privs where
grantee in ('SONU');
'GRANT'||PRIVILEGE||'ON'||GRANTOR||'.'||TABLE_NAME||'TO'||GRANTEE||';'
--------------------------------------------------------------------------------
GRANT UPDATE
ON ASHWANI.MOBILE TO SONU;
GRANT SELECT
ON ASHWANI.MOBILE TO SONU;
