Monday, May 4, 2009

Experiment on EXECUTE IMMEDIATE

Today, We will do an interesting experiment on EXECUTE IMMEDIATE statement. This statement allows us to execute DYNAMIC SQL Statements inside the PLSQL Procedure. It is one of the good feature from Oracle. For SYNTAX and more information about this you can find out in Oracle Documentation.

Now we are going to Create one Database User and then we will write one PLSQL procedure with EXECUTE IMMEDIATE statement.
Lets see,


SQL> -– Login as SYSDBA and Create the user called ‘DEMO’
SQL> show user
USER is "SYS"

SQL> create user demo identified by demo;

User created.

SQL> grant connect,resource to demo;

Grant succeeded.

Database user ‘Demo’ has been created successfully and granted CONNECT, RESOURCE roles to demo user. Okay, It is time to understand some basics of Oracle User Management.

Privilege: A privilege is a permission to execute a SQL Statement or to access another Database User’s Objects. From Oracle Perspective, there are two types or privileges, SYSTEM Privileges like (CREATE TABLE, CREATE VIEW, CREATE PUBLIC SYNONYM etc) and OBJECT Privileges like (SELECT, UPDATE, DELETE, INSERT on Objects eg. TABLE).

Role: A role is a set or group of privileges that can be granted to Users or another Role. “Role is a set of privileges“. Then, What are the privileges that are grouped in CONNECT and RESOURCE Roles?. Let me query the Data Dictionary Tables

SQL> desc role_sys_privs;
Name                       Null?        Type
-------------              --------     ------------   
ROLE                       NOT NULL     VARCHAR2(30)
PRIVILEGE                  NOT NULL     VARCHAR2(40)
ADMIN_OPTION                            VARCHAR2(3)

SQL> select privilege 
  2  from role_sys_privs 
  3  where role = ‘CONNECT’;

PRIVILEGE
--------------
CREATE SESSION

SQL> select privilege 
  2  from role_sys_privs 
  3  where role = ‘RESOURCE’;

PRIVILEGE
-----------------
CREATE SEQUENCE
CREATE TRIGGER
CREATE CLUSTER
CREATE PROCEDURE
CREATE TYPE
CREATE OPERATOR
CREATE TABLE
CREATE INDEXTYPE

8 rows selected.

Alright. Lets continue the experiment, As, i said earlier the user called ‘DEMO’ has been created and granted some roles. Now connect as DEMO user and Create a PLSQL procedure with EXECUTE IMMEDIATE Statement.

SQL> conn demo/demo
Connected.
SQL> show user
USER is "DEMO"

SQL> /*Create a procedure to create table called t and
SQL>  attributes id,name with EXECUTE IMMEDIATE Statement */

SQL> create or replace procedure p
  2  is
  3  begin
  4   EXECUTE IMMEDIATE ‘CREATE TABLE T
  5                     (ID NUMBER, 
  6                      NAME VARCHAR2(20))’;
  7  end;
  8  /

Procedure created.

SQL> -- Created the procedure called “P”. 
SQL> -- Let me execute this procedure
SQL> execute p;
BEGIN p; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at “DEMO.P”, line 4
ORA-06512: at line 1

Oops!, got an error “insufficient privileges” - Do we have this privilege?, YES. we have. It has been grouped under RESOURCE role and the RESOURCE role has been granted to this user (FYI: We have queried the Data Dictionary table to see the privileges that are mapped under RESOURCE role. SEE ABOVE). Then, What is stopping us? Let me grant the CREATE TABLE privilege directly to the DEMO user.

SQL> conn sys@orcl as sysdba
Enter password: *******
Connected.
SQL> show user
USER is "SYS"

SQL> grant CREATE TABLE to DEMO;

Grant succeeded.

SQL> conn demo/demo
Connected.
SQL> show user
USER is "DEMO"

SQL> -- Let me execute the procedure now
SQL> execute p;

PL/SQL procedure successfully completed.

SQL> — Wow! Table ‘T’ got created.
SQL> desc t;
Name                Null?    Type
----                -----    ------------
ID                           NUMBER
NAME                         VARCHAR2(20)

The Conculsion is, In STORED PROCEDURE, roles are DISABLED. Hence any PRIVILEGES granted by a ROLE to USER, will NOT be in effect. The PRIVILEGES MUST be explicitly granted to the user.

Hope you have enjoyed the experiment :-)

Post your suggestions (if any).

4 comments:

Mythili Varadarajan said...

Hi vinoth,

then should the user be given insert and other DML privileges directly inspite of the Resource role?? Please clarify....

Thanks.

Vinoth Kumar said...

Hi Mythili,

DML Privileges - INSERT, UPDATE, DELETE, SELECT are object privileges. Those privileges will be available by default to the user, who is creating the objects (tables, views etc). It will very well work with EXECUTE IMMEDIATE without any extra effort.

This 'CREATE TABLE', 'ALTER TABLE' etc are SYSTEM privileges, it should be granted explicitly through ROLE or DIRECT PRIVILEGE.

At Highlevel, all Privileges that are granted through ROLES will be DISABLED in the PLSQL engine, while you are executing the code.
That is the reason we are granting those SYSTEM privileges directly instead of through ROLEs.

Mythili Varadarajan said...

So now again do u mean that all the privileges will be disabled by the plsql engine except the CREATE PROCEDURE privilege...(this includes the DML privileges also)

or do u mean only the System Privleges will be disabled by the PLSQL engine?

Vinoth Kumar said...

Some of System Privileges granted through ROLE will be disabled