14.4 A Sample Script Here we provide a script that will create a file. This file will contain the commands to grant roles to users and define their default roles. All this information is extracted from the data dictionary. The script file CRUSRGRT.SQL is provided as one example of how to create this file. It is a SQL script that writes another SQL script. This one is a little unusual because it combines conventional SQL with PL/SQL code: doc crusrgrt.sql Arguments: None Combination SQL and PL/SQL script. Creates SQL script to grant roles to users and define user default roles. Output file is mkrolgrt.sql # SET TERMOUT OFF ECHO OFF FEEDBACK OFF PAGESIZE 0 VERIFY OFF SPOOL mkrolgrt.sql -- Get the granted roles and output a grant ... to ... string. SELECT 'grant 'granted_role' to 'grantee DECODE(UPPER(admin_option), 'YES', ' with grant option', null)';' FROM dba_role_privs WHERE grantee NOT IN ('SYS','SYSTEM','SCOTT','DBA','DBSNMP') ORDER BY grantee; -- Default roles are tricky. They must all be declared at once, so use a -- PL/SQL loop to find all of them before writing out the string. SET SERVEROUTPUT ON DECLARE usrname varchar2(35); lastusr varchar2(35) := 'START VALUE'; cmd varchar2(400) := null; CURSOR udr IS SELECT grantee, granted_role FROM dba_role_privs WHERE grantee NOT IN ('SYS','SYSTEM','SCOTT','DBA','DBSNMP') AND UPPER(default_role) = 'YES' ORDER BY grantee; rrec udr%ROWTYPE BEGIN OPEN udr; LOOP FETCH udr INTO rrec; IF rrec.grantee != lastusr -- Has username changed? OR udr%NOTFOUND THEN -- process last record IF cmd IS NOT NULL THEN cmd := 'Alter user 'lastusr' default role 'cmd';'; DBMS_OUTPUT.PUT_LINE(cmd); cmd := null; -- Clear the command string END IF; lastusr := rrec.grantee; -- Save the current username END IF; -- Assemble the default roles into a comma separated string IF cmd IS NOT NULL THEN cmd := cmd', '; END IF; cmd := cmdrrec.granted_role; IF udr%NOTFOUND THEN -- Loop exit test EXIT; END IF; END LOOP; END; / SPOOL OFF SET TERMOUT ON ECHO ON FEEDBACK ON PAGESIZE 24 VERIFY ON The first part of the script is the conventional SQL section. It selects the string that consists of the command to GRANT the role to a user. The DECODE section is used to add the "WITH GRANT OPTION" clause, but only if the value in the admin_option column is "YES." The UPPER function is used, so only a test for uppercase is needed; otherwise , tests for all eight possible combinations would be required. Default role assignment is determined and output by the PL/SQL section. This section starts with the keyword "declare" and ends with the "/" that executes the block. We use PL/SQL because the roles are stored in individual records or rows. If SQL were used to retrieve the default roles, then there would be multiple ALTER USER statements. However, as we've discussed previously, only the last default role assignment is registered. We must have only one statement, and that statement must include all of the default roles. Since SQL does not loop and PL/SQL does, we'll use PL/SQL. To illustrate , when the ALTER USER statement is executed: ALTER USER mary DEFAULT ROLE dba; then that is the only default role the user will have. But most users will have more than one default role. If a subsequent command is given: ALTER USER mary DEFAULT ROLE connect; then the first assignment is discarded and replaced by the second. All default roles must be specified in one command: ALTER USER mary DEFAULT ROLE connect, dba; and the PL/SQL block accomplishes that. On execution, the program output for the CRUSRGRT.SQL script is: GRANT dba TO ralph; GRANT connect TO mary; GRANT dba TO mary; GRANT admin_usr_role TO ed; ALTER USER ralph DEFAULT ROLE dba; ALTER USER mary DEFAULT ROLE connect, dba; ALTER USER ed DEFAULT ROLE admin_usr_role; Note that user mary has been given both roles as defaults in a single statement. A Disclaimer While the CRUSRGRT.SQL script we've described in this chapter works, it would not be suitable for large systems because there is a limit to the number of records PL/SQL can buffer when the DBMS_OUTPUT.PUT_LINE command is used. There are two options you can use in place of this function: -
The records could be written into a table, and, after the PL/SQL block completes, another SQL statement would select the values as part of the current spool file. -
You could use the file input and output functions found in the Oracle-provided UTL_FILE package to open an operating system file and write directly to it. For large systems, two files may be preferred simply to keep down the file size and keep each file performing only one function. The UTL_FILE functions and some documentation may be found in the file UTLFILE.SQL in the RDBMS/ADMIN directory. The exact location of these directories will vary by operating system. |  | 
