Interpretation of Oracle-procedure competence ____oracle

problem

There is a query Dba_ related data dictionary view, compile at the prompt ' table or view does not exist ', compile does not pass.

But the statement of the error can be found in SQL, stating that the problem is not caused by a ' table or view that does not exist '.

Here you need to note that SQL permissions and stored permissions are different.

We are querying the data dictionary under the SYS user and need to display the authorization
Although CC users are also DBA users.

SELECT * from Dba_role_privs where grantee= ' CC ';

Solutions

The Oracle stored procedures Default definition permissions, but role is not visible to stored procedures.
Therefore, the CC user needs to be shown empowering. Using Authid Current_User is not good either.

Using the SYS user

Grant Select any dictionary to CC;

Recompile, through

Knowledge Extension

There are two types of Oracle stored procedures, DR (Definer ' s Rights) Procedure and IR (invoker ' Rights) Procedure.

When executing a stored procedure, we may experience permission problems Authid Definer (defined by): The owner of the compiled storage object. is also the default permission mode. Authid current_user (caller permissions): A pattern that has the current session permission, which may be the same as or different from the current logged-on user (alter session set CURRENT_SCHEMA can change the caller schema)

When you create a stored procedure in a database, the definition user permission is the default mode.

When the authid current_user keyword is specified, it is the caller's permission stored procedure.

The fundamental difference between them is whether role can take effect in the stored procedure . Defining the permissions stored procedure problem

The definition person permission stored procedure role is invalid and must have explicit authorization. Even with DBA role, you cannot access tables for different users.

Sometimes it may take a lot of authorization to execute a stored procedure, which can be a bit cumbersome.

Oracle provides us with a way to use role permissions in stored procedures:
Modify a stored procedure to use Role permissions (caller permissions) when you join Authid Current_User. Chestnut

Compiling through

Execution failed. method One: Use the SYS user to assign permissions

Sql> Conn  sys/****** as SYSDBA;
Connected to Oracle Database 11g Enterprise Edition release 11.2.0.4.0 
Connected as sys@cc as SYSDBA

sql> Grant CREATE table to CC;

Grant succeeded

Re-executing

sql> exec P_test_proc_priv;

Pl/sql procedure successfully completed

Method Two: Modify the stored procedure, add Authid current_user when the stored procedure can use role permission.

Now, let's recycle the permissions and use the other way.

Sql> Conn  sys/****** as SYSDBA;
Connected to Oracle Database 11g Enterprise Edition release 11.2.0.4.0 
Connected as sys@cc as SYSDBA sql> Revok
E CREATE table from CC;

Revoke succeeded

After re-execution:

sql> exec P_test_proc_priv;

Begin P_test_proc_priv; End;

Ora-01031:insufficient privileges
ora-06512:at "CC. P_test_proc_priv ", line 4
Ora-06512:at Line 2

To put it bluntly, this is to have the right to have the current user, if the user can create a table, then this saved through Authid Current_User, also get the user's permissions.

Modifications saved.

Remember to drop the table that has been executed by exec before, otherwise the error is not correct.

Oracle Permissions-related View all system permissions for a user (including system permissions for the role)

Select privilege from Dba_sys_privs where grantee= ' Datauser '  
union  
Select privilege from Dba_sys_privs where Grantee in (select Granted_role from Dba_role_privs where grantee= ' datauser ');   

View system permissions provided by Oracle

Select name from Sys.system_privilege_map a a.name;

Oracle 11g 209 Data