Practical scripts for Oracle permission management

Practical scripts for Oracle permission management

At work, you may be exposed to many environmental problems. For permission problems, you always feel overwhelmed and insufficient. There are too many environments, and you may go wrong when granting permissions, for example, in the following scenario, data is stored in the owner schema. to access the data, you need to create some connected users. All operations cannot be performed directly in the owner schema.

As shown in the following figure, we can define two roles based on the access permission, and grant permissions to them in a unified manner through roles. For example, TESTO_ALL can grant select, insert, delete, and update permissions, while TESTL_SEL can only grant SELECT permissions, so that you can control permissions in a complex environment.

This is the direction we need to work on to make the permission management clearer, but there are always some problems in the use process. For example, after some tables are rebuilt, the permissions will be lost. If the operations are not standardized, some permissions may be lost or excessive permissions may be granted. For example, if you grant the delete permission to a read-only user and DML users only grant the select permission without the update permission. These will cause some problems in use.
Recently, the customer needs to add several read-only users to the environment. However, when assigning permissions, some permissions may always be lost. Sometimes there are thousands of tables involved, because there are many connected users, creating synonyms in connected users makes it really laborious and unrealistic to verify them one by one. Because the environment has been moved by many people, some permissions may have problems, some permissions are lost, and the fixing at the beginning is basically based on the Development feedback.
However, this is indeed relatively passive. I wrote the following script specifically to analyze which permissions are lost and which permissions should not be granted.
Assume that the table owner schema is testo, the table is test1, and testo_sel should only grant select permission. If delete, insert, and update permissions are granted, no more.
Check whether the select permission is missing -- testo_sel

Select t2.grantee, t1.owner, t1.table _ name, t2.privilege rule from all_tables t1, dba_tab_privs t2 where t2.grantee = (select role from dba_roles where role = 'testo _ SEL ') and t2.privilege = 'select' and t1.owner = 'testo' and t1.table _ name = t2.table _ name

Check whether there are additional permissions -- testo_sel. Exclude Insert, delete, and update permissions.
Select t2.grantee, t1.owner, t1.table _ name, t2.privilege no_need_privs from all_tables t1, dba_tab_privs t2 where t1.owner = 'testo' and t1.table _ name = t2.table _ name and t2.grantee = (select role from dba_roles where role = 'testo _ SEL ') and t2.privilege in ('delete', 'insert', 'update ')

The permission rules for testo_all are relatively simple. You only need to determine which permissions should be granted, but do not. The idea is simple, and it is a little bit difficult to do.
The with clause is used to determine which data rows are incomplete from the data rows.
Check for missing select, delete, update, and insert Permissions
With TEMP_DML
(
Select 'insert' temp_dml from dual
Union all
Select 'delete' temp_dml from dual
Union all
Select 'select' temp_dml from dual
Union all
Select 'update' temp_dml from dual
)
Select 'testo _ all' grantee, t1.owner, t1.table _ name, TEMP_DML.temp_dml missing_role_privs from all_tables t1, TEMP_DML where t1.owner = 'testo'
Minus
Select t2.grantee, t1.owner, t1.table _ name, t2.privilege rule from all_tables t1, dba_tab_privs t2 where t2.grantee = (select role from dba_roles where role = 'testo _ all ') and t2.privilege in ('select', 'delete', 'update', 'insert') and t1.owner = 'testo' and t1.table _ name = t2.table _ name