114 - A Disclaimer


Oracle Security
By William Heney, Marlene Theriault
Table of Contents
Chapter 14.  Maintaining User Accounts

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:


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:


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.


Oracle Security
Oracle Security Handbook : Implement a Sound Security Plan in Your Oracle Environment
ISBN: 0072133252
EAN: 2147483647
Year: 1998
Pages: 154

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net