Research on the System Table of Oracle autonomous access control mechanism

I recently focused on database security and roughly studied the independent access control (DAC) and Mandatory Access Control (MAC) of databases ), now I have written my understanding of the DAC in Oracle and shared it with you. All the above documents are based on my understanding. It is inevitable that there are errors. please correct me.

Access control is the process of allowing or forbidding a person to access a certain resource. in a database, access to a database object, such as a table or attempt, is restricted. This access control is generally implemented based on the access control list ACL). ACL generally records what and how access can be accessed by who. Most databases implement ACLs in the form of database system tables. The following describes the design of permission-related system tables in Oracle.

Oracle has two types of permissions: system permission and object permission.

System Permissions

System permissions include database management permissions and ANY permissions. Management permissions such as alter database and create user are DDL-related permissions. Another type of permission with ANY, such as select any table, indicates that all tables can be queried. This type of permission is related to global DML. You can view all system permissions in the SYSTEM_PRIVILEGE_MAP table. The view displays the permission names and their corresponding values. The table shows that the permission values of all system permissions are negative.

Information that records the user's system permission authorization is mainly stored in the system table SYS. SYSAUTH $. You can use the DBMA_METADATA package to obtain the table creation information.

 
 

  
  
SQL> select dbms_metadata.get_ddl('TABLE','SYSAUTH$','SYS') from dual;   
  
  
DBMS_METADATA.GET_DDL('TABLE','SYSAUTH$','SYS')   
  
  
------------------------------------------------------------------------   
  
  
CREATE TABLE "SYS"."SYSAUTH$"   
  
  
(    "GRANTEE#" NUMBER NOT NULL ENABLE,   
  
  
      "PRIVILEGE#" NUMBER NOT NULL ENABLE,   
  
  
      "SEQUENCE#" NUMBER NOT NULL ENABLE,   
  
  
      "OPTION$" NUMBER   
  
  
) PCTFREE   
  
  
PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING  
  
  
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  
  
  
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)  
  
  
TABLESPACE "SYSTEM"　  
  
  
 
 
 

You can see the table field information, including GRANTEE #, PRIVILEGE #, SEQUENCE #, and OPTION $. GRANTEE # indicates the UID of the GRANTEE, and PRIVILEGE # indicates the authorized permission value. OPTION $ indicates whether the permission can be transferred. SEQUENCE # indicates the concept of timestamp, this verification will be followed. It can be seen that the granting of system permissions and roles does not record the information of the authorizer. By querying this table, we can see that the PRIVILEGE # field is not all negative, but also has a positive number. This is because this table not only stores system permissions, the Role authorization information is also stored. If the PRIVILEGE # field is the role ID when the role is granted, the role may also be granted the role and system permissions, therefore, if you want to use a simple SQL statement to obtain all roles or system permissions owned by a user, recursive queries are involved. You can think about how to construct this SQL statement, note: connect by syntax exclusive to Oracle ).

As can be seen from the system table of system permissions, Oracle does not record the concept of the authorizer for system permissions, which indicates that the revocation of system permissions must be performed by specific users, because there is no Authorizer concept, and of course there is no Cascade reclaim system permission, the SEQUENCE # field as a timestamp does not seem to make much sense here.

Object permission

Object permissions mainly record some permissions granted to an object. For example, user A is granted the SELECT permission for table TB1. It can be seen that the object permission system table must have the following fields: Authorizer, grantee, object, permission type, and authorization tag. The object permission system table is SYS. OBJAUTH $. First, let's look at the table definition.

 
 

  
  
SQL> select dbms_metadata.get_ddl('TABLE','OBJAUTH$','SYS') from dual;   
  
  
   
  
  
DBMS_METADATA.GET_DDL('TABLE','OBJAUTH$','SYS')   
  
  
----------------------------------------------------------------------   
  
  
CREATE TABLE "SYS"."OBJAUTH$"   
  
  
 (    "OBJ#" NUMBER NOT NULL ENABLE,   
  
  
      "GRANTOR#" NUMBER NOT NULL ENABLE,   
  
  
      "GRANTEE#" NUMBER NOT NULL ENABLE,   
  
  
      "PRIVILEGE#" NUMBER NOT NULL ENABLE,  
  
  
      "SEQUENCE#" NUMBER NOT NULL ENABLE,  
  
  
      "PARENT" ROWID,  
  
  
      "OPTION$" NUMBER,  
  
  
      "COL#" NUMBER  
  
  
 ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING  
  
  
 
 
 

In the system table, there are not the expected fields, except the object OBJ #), the GRANTOR #), the GRANTEE (GRANTEE #), and the specific permission PRIVELGE #), the transfer mark OPTION $) also has SEQUENCE #, PARENT and COL #. SEQUENCE # should be a timestamp. COL # refers to the column number recorded during row-level authorization, when authorizing a table, you can specify a column, such as grant select (C1) ON T1 TO U1. As for PARENT, we still need to make research.

The above roughly introduces the meaning of the system tables related to permissions in Oracle and the fields in the system tables. Of course, some fields are personal guesses and there is no basis. We still need to conduct experiments to prove this. I am grateful to you for your kindness.