DBA Hub

📋Steps in this guide1/2

GRANT READ PRIVILEGE - Oracle 12c new feature - DBACLASS DBACLASS

GRANT READ PRIVILEGE - Oracle 12c new feature . Lets look at the difference between grant read and grant select privilege.

oracle clusteringintermediate
by OracleDba
14 views
1

GRANT SELECT:

— create a user with grant select privilege create user TEST_USR1 identified by TEST_USR1; grant create session to TEST_USR1; grant SELECT ON dba_raj.testing to test_usr1; with this select privilege, it can run select for update also. SESSION 1( connect with test_usr1) connect test_usr1/test_usr1 select * from dba_raj.testing for update; SESSION 2 ( Open another session as owner of the table): SQL> connect dba_raj/dba_raj Connected. SQL> show user USER is “DBA_RAJ” SQL> update dba_raj.testing set OBJECT_NAME=’TEST’ where owner=’SYS’; <<< it is stuck>> SESSION 3 ( check the blocking session ) : > Get the monitoring script DB MONITORING SCRIPT Get the monitoring script DB MONITORING SCRIPT SQL> @blocker INST_ID BLOCKING_SESSION SID SERIAL# SECONDS_IN_WAIT ———- —————- ———- ———- ————— 1 246 321 2709 30 SQL> @csql Enter value for 1: 321 old 2: (select sql_hash_value from gv$session where sid=&1) new 2: (select sql_hash_value from gv$session where sid=321) SQL_TEXT ——————————————————————————– update dba_raj.testing set OBJECT_NAME=’TEST’ where owner=’SYS’ SQL> @csql Enter value for 1: 246 old 2: (select sql_hash_value from gv$session where sid=&1) new 2: (select sql_hash_value from gv$session where sid=246) SQL_TEXT ——————————————————————————– select * from dba_raj.testing for update We have noticed that despite the user  TEST_USR1   has only GRANT SELECT privilege on dba_raj.testing table, it is able to lock the table .
2

GRANT READ :

— create user with grant read privilege create user TEST_USR2 identified by TEST_USR2; grant create session to TEST_USR2; grant READ ON dba_raj.testing to test_usr2; this user will be able to do select operation as usual: SQL> connect TEST_USR2/TEST_USR2 Connected. SQL> select count(*) from dba_raj.testing; COUNT(*) ———- 85812 Lets try the same command select for update.( It wont allow this operation) SQL> select * from dba_raj.testing for update; select * from dba_raj.testing for update * ERROR at line 1: ORA-01031: insufficient privileges With oracle 12, its time to revoke GRANT SELECT and use GRANT READ privilege Similar to GRANT SELECT ANY , we can use GRANT READ ANY  privilege

Comments (0)

Please to add comments

No comments yet. Be the first to comment!