Table function operations with Oracle Permissions

Do you have a headache in obtaining permissions on Oracle Objects? If this is the case, the following articles will give you corresponding solutions. The following articles mainly describe how to obtain Oracle permissions, that is, to obtain Oracle permissions through table function.

System permission ROLE permission. Oracle provides a System View starting with DBA _ * ROLE _ * SESSION _ * For you to query the permission allocation information, we can also obtain these permissions through other tools such as OEM. Here we use table function to obtain these Oracle permissions.

Declaration, you can also use the authorization mode on other users.

Create package:

1: Create an object

 
 

  
  
SQL>create type PrivsList is table of varchar2(4000);  
  
  
SQL>/  
 
 

2: Create a package

The package only lists some common functions for obtaining Oracle permissions.

Create or replace package ManPrivs is

-Author: LIYAN

-Created: 2009-8-26 10:06:18

-Purpose:

When executing this package, you must have the read permission on dba_sys_privs, dba_role_privs, role_sys_privs, role_tab_privs, and dba_tables.

And has DBA authorization capability

We recommend that you use OEM to grant and revoke system and role permissions.

We recommend that you use OEM to query the permissions of a role.

 
 

  
  
Function ListTabPrivs(ObjOwner varchar2 , 
ObjName varchar2 default null, Privs varchar2 default null, 
IsRevoke varchar2 default ‘N’,  
  
  
Grantee varchar2 default null) return PrivsList pipelined;  
 
 

View the table Oracle permissions of an object

 
 

  
  
Example:  
  
  
select * from table(manprivs.ListTabPrivs(‘sh,hr’,“,‘select,update,delete,insert’,'N‘,'PUBLIC’));  
  
  
*/  
  
  
Function ListSysPrivs(ObjOwner varchar2 default null) return PrivsList pipelined;  
 
 

System permissions of production object

 
 

  
  
Example:  
  
  
select * from table(ManPrivs.ListSysPrivs(‘sh,hr’));  
  
  
*/  
  
  
Function ListRolePrivs(ObjOwner varchar2 default null) return PrivsList pipelined ;  
 
 

View the role Oracle permissions of an object

 
 

  
  
Example:  
  
  
select * from table(ManPrivs.ListRolePrivs(‘sh,hr’));  
  
  
*/  
  
  
Function ListRoleSysPrivs(ObjOwner varchar2 default null) return PrivsList pipelined ;  
 
 

View system permissions of a role on an object

 
 

  
  
Example:  
  
  
select * from table(ManPrivs.ListRoleSysPrivs(‘sh,hr’));  
  
  
*/  
  
  
Function ListRoleTabPrivs(ObjOwner varchar2 default null) return PrivsList pipelined ;  
 
 

View the table permissions of a role on an object

 
 

  
  
Example:  
  
  
SELECT * fROM TABLE(MANPRIVS.ListRoleTabPrivs(‘SH,HR’))  
  
  
*/  
  
  
Function ListRoleRolePrivs(ObjOwner varchar2 default null) return PrivsList pipelined ;  
  
  
Procedure RevokeTabPrivs(ObjOwner varchar2, ObjName varchar2 default null, Privs varchar2 default null,  
  
  
Grantee varchar2 default null);  
 
 

Revoke the table Oracle permission of an object

 
 

  
  
Example:  
  
  
Execute ManPrivs.RevokeTabPrivs(‘HR’,“,”,‘PUBLIC’)  
  
  
*/  
  
  
Procedure GrantTabPrivs(ObjOwner varchar2, ObjName varchar2, Privs varchar2,  
  
  
Grantee varchar2);  
 
 

Grant special Oracle permissions to specific objects

 
 

  
  
Example:  
  
  
Execute ManPrivs.GrantTabPrivs(‘hr’,'jobs‘,’select,update,insert‘,'public’)  
  
  
*/  
  
  
end ManPrivs;  
 
 

The above content describes how to obtain Oracle permissions through table function, hoping to help you in this regard.