size=+0>
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 := cmd||rrec.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. |
| 
No comments:
Post a Comment