which they do not have any privileges?

In your multitenant container database (CDB) containing pluggable database (PDBs), the HR user
executes the following commands to create and grant privileges on a procedure:
CREATE OR REPLACE PROCEDURE create_test_v (v_emp_id NUMBER, v_ename
VARCHAR2, v_SALARY NUMBER, v_dept_id NUMBER)
BEGIN
INSERT INTO hr.test VALUES (V_emp_id, V_ename, V_salary, V_dept_id);
END;
/
GRANT EXECUTE ON CREATE_TEST TO john, jim, smith, king;
How can you prevent users having the EXECUTE privilege on the CREATE_TEST procedure from
inserting values into tables on which they do not have any privileges?

In your multitenant container database (CDB) containing pluggable database (PDBs), the HR user
executes the following commands to create and grant privileges on a procedure:
CREATE OR REPLACE PROCEDURE create_test_v (v_emp_id NUMBER, v_ename
VARCHAR2, v_SALARY NUMBER, v_dept_id NUMBER)
BEGIN
INSERT INTO hr.test VALUES (V_emp_id, V_ename, V_salary, V_dept_id);
END;
/
GRANT EXECUTE ON CREATE_TEST TO john, jim, smith, king;
How can you prevent users having the EXECUTE privilege on the CREATE_TEST procedure from
inserting values into tables on which they do not have any privileges?

A.
Create the CREATE_TEST procedure with definer’s rights.

B.
Grant the EXECUTE privilege to users with GRANT OPTION on the CREATE_TEST
procedure.

C.
Create the CREATE_TEST procedure with invoker’s rights.

D.
Create the CREATE_TEST procedure as part of a package and grant users the EXECUTE
privilege the package.

Explanation:
If a program unit does not need to be executed with the escalated privileges of the
definer, you should specify that the program unit executes with the privileges of the caller, also
known as the invoker. Invoker’s rights can mitigate the risk of SQL injection.

Incorrect:
Not A: By default, stored procedures and SQL methods execute with the privileges of their owner,
not their current user. Such definer-rights subprograms are bound to the schema in which they
reside.
not B: Using the GRANT option, a user can grant an Object privilege to another user or to
PUBLIC.

Show Hint

Leave a Reply 4

Your email address will not be published. Required fields are marked *

Pippo

Pippo

A

SQL> create user pippo identified by pippo;
grant connect, resource to pippo;
create user hr identified by hr;
grant connect, resource to hr;
create user smith identified by smith;
grant connect, resource to smith;
—
create table hr.test (
emp_id NUMBER (10),
ename VARCHAR2(100),
SALARY NUMBER (10),
dept_id NUMBER(19));
User created.

SQL> conn / as sysdba

SQL> grant insert on hr.test to pippo;

SQL> conn pippo/pippo

SQL> CREATE OR REPLACE PROCEDURE create_test_v (v_emp_id NUMBER, v_ename
VARCHAR2, v_SALARY NUMBER, v_dept_id NUMBER) as
BEGIN
INSERT INTO hr.test VALUES (V_emp_id, V_ename, V_salary, V_dept_id);
END;
/ 2 3 4 5 6

SQL> conn / as sysdba

SQL> GRANT EXECUTE ON pippo.CREATE_TEST_v to smith;

SQL> conn smith/smith

SQL> exec pippo.CREATE_TEST_v(1,’pippo’,10,10);

PL/SQL procedure successfully completed.

SQL> conn / as sysdba

SQL> select * from hr.test;

EMP_ID
———-
ENAME
——————————————————————————–
SALARY DEPT_ID
———- ———-
1
pippo
10 10

Reply

PonyX

PonyX

@pippo your answer doesn’t make any sense.

You granted pippo with insert privilege on HR.TEST, and created the procedure with definer’s right (default). Any user granted execute privilege can insert data into it.

The question is asking how to “prevent users from” inserting the data if they were not granted the INSERT privilege”.

the correct answer is C, you can try it out.

Reply