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

This article is about how to test the cascading recovery of Privilege Oracle object permissions. The editor thinks it is very practical, so I share it with you. I hope you can get something after reading this article. Let's take a look at it.

Oracle permissions are divided into system permissions and object permissions.

This paper discusses the cascading recovery effect of Oracle object permissions, which is opposite to the effect of system permissions: Oracle cascades reclaim object permissions.

Three users were involved in the experiment: secooler1 users, secooler2 users and secooler3 users.

1. Clean up and initialize users

1) Clean up test users secooler1, secooler2, and secooler3

Sys@secdb > conn / as sysdba

Connected.

Sys@secdb > drop user secooler1 cascade

User dropped.

Sys@secdb > drop user secooler2

User dropped.

Sys@secdb > drop user secooler3

User dropped.

2) create secooler1, secooler2, and secooler3 users and grant the most basic system permissions

(1) create a secooler1 user and grant permissions to create sessions, create tables, and tablespaces for subsequent creation of test tables in secooler1 users.

Sys@secdb > create user secooler1 identified by secooler1

User created.

Sys@secdb > grant create session,create table,unlimited tablespace to secooler1

Grant succeeded.

(2) create a secooler2 user and grant the most basic permissions to create a session

Sys@secdb > create user secooler2 identified by secooler2

User created.

Sys@secdb > grant create session to secooler2

Grant succeeded.

(3) similarly, create a secooler3 user and grant the most basic permissions to create a session

Sys@secdb > create user secooler3 identified by secooler3

User created.

Sys@secdb > grant create session to secooler3

Grant succeeded.

two。 Create database objects and complete object permission authorization

1) create table T and initialize the data under the secooler1 user.

Here, create a table under the secooler1 user as an example

Sys@secdb > conn secooler1/secooler1

Connected.

Secooler1@secdb > create table t (x int)

Table created.

Secooler1@secdb > insert into t values (1)

1 row created.

2) under the secooler1 user, grant the object permission to query the T table to secooler2 with the with grant option option.

Secooler1@secdb > grant select on t to secooler2 with grant option

Grant succeeded.

3) Grant object permissions to secooler3 under the secooler2 user

Secooler1@secdb > conn secooler2/secooler2

Connected.

Secooler2@secdb > grant select on secooler1.t to secooler3

Grant succeeded.

3. Verify the result of object permission grant

1) verify the result of object permission grant through query

Secooler2@secdb > select * from secooler1.t

X

-

one

Secooler2@secdb > conn secooler3/secooler3

Secooler3@secdb > select * from secooler1.t

X

-

one

Object permissions are used normally.

2) confirm by viewing the permissions of the object

Secooler2@secdb > conn / as sysdba

Connected.

Sys@secdb > col GRANTEE for A9

Sys@secdb > col OWNER for A9

Sys@secdb > col TABLE_NAME for A5

Sys@secdb > col GRANTOR for A9

Sys@secdb > col PRIVILEGE for A9

Sys@secdb > select * from dba_tab_privs where grantee='SECOOLER2'

GRANTEE OWNER TABLE GRANTOR PRIVILEGE GRA HIE

SECOOLER2 SECOOLER1 T SECOOLER1 SELECT YES NO

Sys@secdb > select * from dba_tab_privs where grantee='SECOOLER3'

GRANTEE OWNER TABLE GRANTOR PRIVILEGE GRA HIE

SECOOLER3 SECOOLER1 T SECOOLER2 SELECT NO NO

To view the results, the object permission grant information appears normally.

4. Retrieve the object permissions of the secooler2 user in the secooler1 user

Sys@secdb > conn secooler1/secooler1

Connected.

Secooler1@secdb > revoke select on t from secooler2

Revoke succeeded.

5. View cascading deletion effect

1) confirm by querying permissions

Secooler3@secdb > conn / as sysdba

Connected.

Sys@secdb > select * from dba_tab_privs where grantee='SECOOLER2'

No rows selected

Sys@secdb > select * from dba_tab_privs where grantee='SECOOLER3'

No rows selected

It can be seen that the object permissions that secooler2 once granted to secooler3 users are also cascaded back. Both users no longer have object permissions.

2) confirm through object query test

Secooler1@secdb > conn secooler2/secooler2

Connected.

Secooler2@secdb > select * from secooler1.t

Select * from secooler1.t

*

ERROR at line 1:

ORA-00942: table or view does not exist

Secooler2@secdb > conn secooler3/secooler3

Connected.

Secooler3@secdb > select * from secooler1.t

Select * from secooler1.t

*

ERROR at line 1:

ORA-00942: table or view does not exist

It can be seen that the object permissions of both secooler2 and secooler3 are revoked.

6. Summary

When secooler1 reclaims the object permissions of secooler2, it will also withdraw the object permissions of secooler3. This is Oracle's policy on cascading object permissions.

The above is how to test the cascading recovery phenomenon of Privilege Oracle object permissions. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.