A procedure is created in the SYS schema to allow users to change the password as follows:
CREATE OR REPLACE
PROCEDURE change_password(p_username VARCHAR2 DEFAULT NULL,
p_new_password VARCHAR2 DEFAULT NULL)
IS
v_sql_stmt VARCHAR2(500);
BEGIN
v_sql_stmt := ‘ALTER USER ‘||p_username ||’ IDENTIFIED BY ‘
|| p_new_password;
EXECUTE IMMEDIATE v_sql_stmt;
END change_password;
The SYS user has granted EXECUTE privilege on the procedure to the OE user. But OE is able
to change the password for SYS by using this procedure. How would you protect this?
A.
by using the procedure as part of a PL/SQL package
B.
by using a bind argument with dynamic SQL in the procedure
C.
by using AUTHID DEFINER in the procedure to implement the definer’s right
D.
by using AUTHID CURRENT_USER in the procedure to implement the invoker’s right
D
D
D.
Using Invoker’s Rights Versus Definer’s Rights (AUTHID Clause)
The AUTHID clause, which makes stored procedures and SQL methods execute with the
privileges and schema context of the calling user.
How External References Are Resolved in Invoker’s Rights Subprograms
If you specify AUTHID CURRENT_USER, the privileges of the current user are checked at run time,
and external references are resolved in the schema of the current user.
However, this applies only to external references in:
– SELECT, INSERT, UPDATE, and DELETE data manipulation statements
– The LOCK TABLE transaction control statement
– OPEN and OPEN-FOR cursor control statements
– EXECUTE IMMEDIATE and OPEN-FOR-USING dynamic SQL statements
– SQL statements parsed using DBMS_SQL.PARSE()
http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/subprograms.htm#i18574