Database Reference
In-Depth Information
BEGIN
--INPUT VALIDATION OF THE USER PASSWORD TO MAKE SURE IT DOES NOT CONTAIN SQL COMMANDS
l_password_quoted := SYS.DBMS_ASSERT.ENQUOTE_NAME(pi_new_password);--quote the password if
it is not already.
l_password_not_quoted := REPLACE(l_password_quoted,'"','');--replace the quotes with blank
l_password_validated := DBMS_ASSERT.simple_sql_name(l_password_not_quoted);--validate the
non-quoted password
-- 1. Run the command immediately
l_exec_string := l_exec_string || USER || ' IDENTIFIED BY ' || l_password_validated;
l_stage := 'Executing '|| l_exec_string;
EXECUTE IMMEDIATE (l_exec_string);
END change_password;
END UM_USER;
DBMS_ASSERT will block the insertion of additional SQL onto the end of the inputted password, such as granting
proxy access through another account. This example could be expanded to check that the ALTER USER command was
not affecting protected users like SYS or applications accounts.
Grant Schema Wide
Since ROLEs granted to schema owners are not passed through Definer's Rights to its procedures, individual direct
object privilege grants are required. It can be quite cumbersome to grant the required direct-object privileges on all
the objects within a schema, so use this cursor loop to automate this process.
create or replace PROCEDURE grant_schema_wide_select (pi_username IN varchar2, pi_schema IN
varchar2) IS PRAGMA AUTONOMOUS_TRANSACTION;
l_exec_string VARCHAR2(1024) := 'GRANT SELECT ON ';
l_object_name varchar2(30);
l_grant_count NUMBER := 0;
CURSOR csr_return_sel_objs_for_schema IS
SELECT OBJECT_NAME
FROM DBA_OBJECTS
WHERE OWNER = UPPER(pi_schema)
AND OBJECT_TYPE IN ('TABLE', 'VIEW', 'SEQUENCE')
AND OBJECT_NAME NOT LIKE 'BIN$%'
AND STATUS = 'VALID';
BEGIN
OPEN csr_return_sel_objs_for_schema;
LOOP
FETCH csr_return_sel_objs_for_schema INTO l_object_name;
EXIT WHEN csr_return_sel_objs_for_schema%NOTFOUND;
l_exec_string := 'GRANT SELECT ON '|| UPPER(pi_schema) ||'.'|| l_object_name ||' TO '||
UPPER(pi_username);
EXECUTE IMMEDIATE l_exec_string;
l_grant_count := l_grant_count + 1;
END LOOP;
