Shulou(Shulou.com)06/01 Report--

The recall permission granted by Oracle uses grant and revoke statements, but will it take effect as soon as the authorization and recall permission statements are executed? In addition, the permissions of Oracle are divided into system permissions, role permissions and object permissions. What is the effective time of grant and revoke of these three permissions? Let's take a look at what the official document says:

Depending on what is granted or revoked, a grant or revoke takes effect at different times:

All grants and revokes of system and object privileges to anything (users, roles, and PUBLIC) take immediate effect.

All grants and revokes of roles to anything (users, other roles, PUBLIC) take effect only when a current user session issues a SET ROLE statement to reenable the role after the grant and revoke, or when a new user session is created after the grant or revoke.

You can see which roles are currently enabled by examining the SESSION_ROLES data dictionary view.

As we can see from the above description, grant and revoke system permissions and object permissions take effect immediately, while grant or revoke roles do not take effect immediately for the current session, unless the role is enabled with the set role statement or the session is reconnected.

Let's take 11.2.0.4 as an example to test whether it is consistent with the description of the official document.

First, create a test user and give connect the role

Sys@ORCL > create user zhaoxu identified by zhaoxu;User created.sys@ORCL > grant connect to zhaoxu;Grant succeeded.sys@ORCL > select * from dba_role_privs where grantee='ZHAOXU' GRANTEE GRANTED_ROLE ADMIN_OPT DEFAULT_R-- ZHAOXU CONNECT NO YESsys@ORCL > select * from dba_sys_privs where grantee='ZHAOXU' No rows selectedsys@ORCL > select * from dba_tab_privs where grantee='ZHAOXU';no rows selectedsys@ORCL > conn zhaoxu/zhaoxuConnected.zhaoxu@ORCL > select * from session_roles;ROLE---CONNECTzhaoxu@ORCL > select * from session_privs PRIVILEGE---CREATE SESSIONzhaoxu@ORCL > create table t (id number) segment creation immediate;create table t (id number) * ERROR at line 1:ORA-01031: insufficient privileges

Today's zhaoxu users only have the CONNECT role, can only connect to the database, and can do nothing else.

2. Test the grant and revoke of system permissions and object permissions

Now open another session and assign system privilege to the zhaoxu user

-- session 2sys@ORCL > grant create table,unlimited tablespace to zhaoxu;Grant succeeded.--session 1zhaoxu@ORCL > select * from session_privs PRIVILEGE- -CREATE SESSIONUNLIMITED TABLESPACECREATE TABLEzhaoxu@ORCL > select * from session_roles ROLE-CONNECTzhaoxu@ORCL > create table t (id number) segment creation immediate Table created.-- uses segment creation immediate to avoid the impact of 11g delay in creating new feature segments

If nothing is done in Plenary session 1 when giving zhaoxu users create table and unlimited tablespace system permissions, the permissions will take effect immediately.

Test the revoke permissions again.

-- session 2sys@ORCL > revoke unlimited tablespace from zhaoxu;Revoke succeeded.--session 1zhaoxu@ORCL > create table T1 (id number) segment creation immediate;create table T1 (id number) segment creation immediate*ERROR at line 1:ORA-01950: no privileges on tablespace 'USERS'zhaoxu@ORCL > select * from session_privs PRIVILEGE-CREATE SESSIONCREATE TABLE

You can also see that the recycling operation can take effect immediately, and there is no need for the existing session to do anything.

Grant and revoke of test object permissions

-- grant test-- session 1zhaoxu@ORCL > select count (*) from zx.t;select count (*) from zx.t * ERROR at line 1:ORA-00942: table or view does not exist--session 2sys@ORCL > grant select on zx.t to zhaoxu;Grant succeeded.sys@ORCL > select * from dba_tab_privs where grantee='ZHAOXU' GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIERARCHY -ZHAOXU ZX T ZX SELECT NO NO--session 1zhaoxu@ORCL > select count (*) from zx.t COUNT (*)-99999zhaoxu@ORCL > select * from session_privs PRIVILEGE- -CREATE SESSIONCREATE TABLE--revoke test-- session 2sys@ORCL > revoke select on zx.t from zhaoxu Revoke succeeded.sys@ORCL > select * from dba_tab_privs where grantee='ZHAOXU';no rows selected--session 1zhaoxu@ORCL > select count (*) from zx.t;select count (*) from zx.t * ERROR at line 1:ORA-00942: table or view does not exist

Grant and revoke operations on object permissions are consistent with system permissions, and all commands take effect immediately, including for connected sessions.

3. Grant and revoke of the test role

Today's zhaoxu users still have only the connect role and have opened a session

-- session 2sys@ORCL > select * from dba_role_privs where grantee='ZHAOXU' GRANTEE GRANTED_ROLE ADMIN_OPT DEFAULT_R-- ZHAOXU CONNECT NO YES--session 1zhaoxu@ORCL > select * from session_roles ROLE--CONNECT

Test grant DBA permissions

-- session 1 View roles in the session zhaoxu@ORCL > select * from session_roles;ROLE-CONNECT--session 2 assign zhaoxu users the dba role sys@ORCL > grant dba to zhaoxu Grant succeeded.sys@ORCL > select * from dba_role_privs where grantee='ZHAOXU' GRANTEE GRANTED_ROLE ADMIN_OPT DEFAULT_R-- ZHAOXU DBA NO YESZHAOXU CONNECT NO YES--session 1 looks at the roles in the session again There is no dba role and no permission to view v$session zhaoxu@ORCL > select * from session_roles ROLE-CONNECTzhaoxu@ORCL > select count (*) from v$session Select count (*) from v$session * ERROR at line 1:ORA-00942: when table or view does not exist--session 1 executes the set role command, you can see that DBA and related roles have been loaded into session1. You can also query v$sessionzhaoxu@ORCL > set role dba;Role set.zhaoxu@ORCL > select * from session_roles. ROLE-DBASELECT_CATALOG_ROLEHS_ADMIN_SELECT_ROLE.19 rows selected.zhaoxu@ORCL > select count (*) from v$session COUNT (*)-29Mui-using zhaoxu users to open session 3, you can see that DBA and related roles [oracle@rhel6 ~] $sqlplus zhaoxu/zhaoxuSQL*Plus: Release 11.2.0.4.0 Production on Sat Jan 21 16:22:01 2017Copyright (c) 1982, 2013, Oracle are loaded by default in the new session. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionszhaoxu@ORCL > select * from session_roles ROLE-CONNECTDBASELECT_CATALOG_ROLE.20 rows selected.

Test the revoke DBA role

-- session 2 reclaims DBA roles sys@ORCL > revoke dba from zhaoxu;Revoke succeeded.sys@ORCL > select * from dba_role_privs where grantee='ZHAOXU' GRANTEE GRANTED_ROLE ADMIN_OPT DEFAULT_R-- ZHAOXU CONNECT NO YES--session 3 View the role of the session There are still DBA and related roles zhaoxu@ORCL > select * from session_roles ROLE-CONNECTDBASELECT_CATALOG_ROLE.20 rows selected.-- uses zhaoxu user to open session 4 Check out only the CONNECT role [oracle@rhel6 ~] $sqlplus zhaoxu/zhaoxuSQL*Plus: Release 11.2.0.4.0 Production on Sat Jan 21 16:30:19 2017Copyright (c) 1982, 2013, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionszhaoxu@ORCL > select * from session_roles ROLE-CONNECT--session 3 executes the set role command zhaoxu@ORCL > set role dba Set role dba*ERROR at line 1:ORA-01924: role 'DBA' not granted or does not existzhaoxu@ORCL > set role all;Role set.zhaoxu@ORCL > select * from session_roles;ROLE-CONNECT

It can be concluded from the above tests that grant and revoke system and object permissions take effect immediately, while grant or revoke roles do not take effect immediately for the current session unless the role is enabled using the set role statement or the setting is reconnected to the session. Consistent with the description of the official document.

But one question is what if you look at the role owned by other sessions that are already connected?

Official document: http://docs.oracle.com/cd/E11882_01/network.112/e36292/authorization.htm#DBSEG99974

System privilege: http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9013.htm#BABEFFEE

Object privilege: http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9013.htm#BGBCIIEG

Set role: http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_10004.htm#SQLRF01704

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.