Privileged Communications

Within the past decade (or longer) more and more people have access to relational databases, for work, fun, banking, you name it. This, of course, can make the DBA’s life more complicated, sorting out the necessary privileges such users need and ensuring that a path of least privilege is followed. Le’s look at what privileges are available in an Oracle database and why caution is a good thing.

As most know there are two roles pre-defined in every Oracle database:

DBA
SYSDBA

Even though both are extremely privileged they do differ considerably. DBA is the ‘standard’ role granted to, you guessed it, DBAs in the database. This level of access grants considerable power to the associated user, such as administering tablespaces, creating and modifying users, creating tables and indexes in any schema in the database, dropping tables in any schema in the database, running database health checks via ASH and AWR reports and other performance-related activities. It does NOT allow users granted this role the ability to startup or shutdown a database.

SYSDBA is not really a role, it’s more of an access level. It can be granted to users, but except for extremely rare cases (for example the end of the world as we know it) it should not even be considered. By default SYS is the only account afforded this privileged access, and it should remain that way. SYSDBA is, essentially, ‘root’ access to Oracle — connected as SYSDBA opens the floodgates of privilege so that databases can be created or dropped, stopped, started, and altered in various ways. This is a powerful level of access, one that should not be dispensed lightly.

This now leaves a broad range of privileges that can be granted, a la carte, to users and roles to provide various levels of access, from a read-only user that can be used for reporting to users with read and write access to one or more schemas (other than the user’s own). Additionally privileges to create packages, functions, procedures, sequences and triggers can also be granted, usually to developer-level accounts. These lists of privileges can be modified as needed to add or remove specific grants. Such grants can be made to the user account itself or to a role that account is assigned. There are advantages and disadvantages to both. Granting to a role ensures that all developers get exactly the same privileges; the down side to that is the possibility that some actions may not be possible through privileges granted through a role. Privileges granted directly to the user account won’t have this limitation, but do complicate the landscape by having the DBA manage the per-user list of grants and, should a privilege be removed, force the DBA to manage each user separately to ensure no user has privileges they shouldn’t.

The full list of SYS-level privileges is:

ADMINISTER ANY SQL TUNING SET, ADMINISTER DATABASE TRIGGER, ADMINISTER RESOURCE MANAGER, ADMINISTER SQL MANAGEMENT OBJECT, ADMINISTER SQL TUNING SET, ADVISOR,
ALTER ANY ASSEMBLY, ALTER ANY CLUSTER, ALTER ANY CUBE, ALTER ANY CUBE DIMENSION, ALTER ANY DIMENSION, ALTER ANY EDITION, ALTER ANY EVALUATION CONTEXT,
ALTER ANY INDEX, ALTER ANY INDEXTYPE, ALTER ANY LIBRARY, ALTER ANY MATERIALIZED VIEW, ALTER ANY MINING MODEL, ALTER ANY OPERATOR, ALTER ANY OUTLINE, ALTER ANY PROCEDURE,
ALTER ANY ROLE, ALTER ANY RULE, ALTER ANY RULE SET, ALTER ANY SEQUENCE, ALTER ANY SQL PROFILE, ALTER ANY TABLE, ALTER ANY TRIGGER, ALTER ANY TYPE, ALTER DATABASE,
ALTER PROFILE, ALTER RESOURCE COST, ALTER ROLLBACK SEGMENT, ALTER SESSION, ALTER SYSTEM, ALTER TABLESPACE, ALTER USER, ANALYZE ANY, ANALYZE ANY DICTIONARY, AUDIT ANY,
AUDIT SYSTEM, BACKUP ANY TABLE, BECOME USER, CHANGE NOTIFICATION, COMMENT ANY MINING MODEL, COMMENT ANY TABLE, CREATE ANY ASSEMBLY, CREATE ANY CLUSTER, CREATE ANY CONTEXT,
CREATE ANY CUBE, CREATE ANY CUBE BUILD PROCESS, CREATE ANY CUBE DIMENSION, CREATE ANY DIMENSION, CREATE ANY DIRECTORY, CREATE ANY EDITION, CREATE ANY EVALUATION CONTEXT,
CREATE ANY INDEX, CREATE ANY INDEXTYPE, CREATE ANY JOB, CREATE ANY LIBRARY, CREATE ANY MATERIALIZED VIEW, CREATE ANY MEASURE FOLDER, CREATE ANY MINING MODEL, CREATE ANY OPERATOR,
CREATE ANY OUTLINE, CREATE ANY PROCEDURE, CREATE ANY RULE, CREATE ANY RULE SET, CREATE ANY SEQUENCE, CREATE ANY SQL PROFILE, CREATE ANY SYNONYM, CREATE ANY TABLE,
CREATE ANY TRIGGER, CREATE ANY TYPE, CREATE ANY VIEW, CREATE ASSEMBLY, CREATE CLUSTER, CREATE CUBE, CREATE CUBE BUILD PROCESS, CREATE CUBE DIMENSION, CREATE DATABASE LINK,
CREATE DIMENSION, CREATE EVALUATION CONTEXT, CREATE EXTERNAL JOB, CREATE INDEXTYPE, CREATE JOB, CREATE LIBRARY, CREATE MATERIALIZED VIEW, CREATE MEASURE FOLDER, CREATE MINING MODEL,
CREATE OPERATOR, CREATE PROCEDURE, CREATE PROFILE, CREATE PUBLIC DATABASE LINK, CREATE PUBLIC SYNONYM, CREATE ROLE, CREATE ROLLBACK SEGMENT, CREATE RULE, CREATE RULE SET,
CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE TABLESPACE, CREATE TRIGGER, CREATE TYPE, CREATE USER, CREATE VIEW, DEBUG ANY PROCEDURE, DEBUG CONNECT SESSION,
DELETE ANY CUBE DIMENSION, DELETE ANY MEASURE FOLDER, DELETE ANY TABLE, DEQUEUE ANY QUEUE, DROP ANY ASSEMBLY, DROP ANY CLUSTER, DROP ANY CONTEXT, DROP ANY CUBE, DROP ANY CUBE BUILD PROCESS,
DROP ANY CUBE DIMENSION, DROP ANY DIMENSION, DROP ANY DIRECTORY, DROP ANY EDITION, DROP ANY EVALUATION CONTEXT, DROP ANY INDEX, DROP ANY INDEXTYPE, DROP ANY LIBRARY,
DROP ANY MATERIALIZED VIEW, DROP ANY MEASURE FOLDER, DROP ANY MINING MODEL, DROP ANY OPERATOR, DROP ANY OUTLINE, DROP ANY PROCEDURE, DROP ANY ROLE, DROP ANY RULE, DROP ANY RULE SET,
DROP ANY SEQUENCE, DROP ANY SQL PROFILE, DROP ANY SYNONYM, DROP ANY TABLE, DROP ANY TRIGGER, DROP ANY TYPE, DROP ANY VIEW, DROP PROFILE, DROP PUBLIC DATABASE LINK, DROP PUBLIC SYNONYM,
DROP ROLLBACK SEGMENT, DROP TABLESPACE, DROP USER, ENQUEUE ANY QUEUE, EXECUTE ANY ASSEMBLY, EXECUTE ANY CLASS, EXECUTE ANY EVALUATION CONTEXT, EXECUTE ANY INDEXTYPE, EXECUTE ANY LIBRARY,
EXECUTE ANY OPERATOR, EXECUTE ANY PROCEDURE, EXECUTE ANY PROGRAM, EXECUTE ANY RULE, EXECUTE ANY RULE SET, EXECUTE ANY TYPE, EXECUTE ASSEMBLY, EXPORT FULL DATABASE, FLASHBACK ANY TABLE,
FLASHBACK ARCHIVE ADMINISTER, FORCE ANY TRANSACTION, FORCE TRANSACTION, GLOBAL QUERY REWRITE, GRANT ANY OBJECT PRIVILEGE, GRANT ANY PRIVILEGE, GRANT ANY ROLE, IMPORT FULL DATABASE,
INSERT ANY CUBE DIMENSION, INSERT ANY MEASURE FOLDER, INSERT ANY TABLE, LOCK ANY TABLE, MANAGE ANY FILE GROUP, MANAGE ANY QUEUE, MANAGE FILE GROUP, MANAGE SCHEDULER, MANAGE TABLESPACE,
MERGE ANY VIEW, ON COMMIT REFRESH, QUERY REWRITE, READ ANY FILE GROUP, RESTRICTED SESSION, RESUMABLE, SELECT ANY CUBE, SELECT ANY CUBE DIMENSION, SELECT ANY DICTIONARY,
SELECT ANY MINING MODEL, SELECT ANY SEQUENCE, SELECT ANY TABLE, SELECT ANY TRANSACTION, UNDER ANY TABLE, UNDER ANY TYPE, UNDER ANY VIEW, UNLIMITED TABLESPACE, UPDATE ANY CUBE,
UPDATE ANY CUBE BUILD PROCESS, UPDATE ANY CUBE DIMENSION, UPDATE ANY TABLE

It’s quite a long list, indeed. Some privileges, such as CREATE TABLE. CREATE SEQUENCE. DROP TABLE and similar, are usually safe to grant to development users since these will apply to objects created in their own schemas. Other, more “invasive” privileges, such as the “ANY” privileges, should be reserved for more administrative users as they can be used to create, drop and alter any object in the database, including those owned by SYS and SYSTEM. A general rule to follow would be to avoid granting such far-reaching privileges unless it’s absolutely necessary. What, exactly, defines “absolutely necessary”? One rare possibility is the “need” to create tables in a different schema than the user’s own. This, of course, is not the best reason for such access but other possibilities, such as creating a dedicated, shared development account, don’t appear to be much better. In the end it’s up to the DBA to decide how to handle such situations, and to be prepared to defend that decision should it be necessary.

Oracle does make using roles to grant privileges easier by providing the authid pragma for functions, packages and procedures. This allows users to utilize the grants given to them through roles to execute these objects by directing Oracle to access the user’s and role’s privilege lists. Early on in the development and maturation of Oracle using packages, procedures and functions required direct grants on the objects involved to a user account attempting to use them. Using the authid pragma can toggle between the default behavior (authid owner) to allowing any user with privileges, granted directly or indirectly (authid current_user). This simplifies security in the database, allowing the DBA to assign pre-defined roles for access and still providing access to PL/SQL objects. There may be specific cases where a direct grant is required, but the incidence of this is small and it reduces the number of direct grants a DBA must manage for any given user.

Generally speaking using roles to encapsulate and distribute privileges is a preferred method. Given that it would behoove the intrepid DBA to catalog existing privilege sets this is where he or she should start this journey. Once documented role names can be decided and privilege sets granted. The time-consuming portion is to convert the direct grants to the role-based model, revoking the direct privileges and granting the newly created roles. Of course testing this model may reveal problems only direct grants can solve, but it should be expected that these situations will be few. Once these issues are resolved a script, similar to that posted below, can be used to generate scripts that can be modified to create any user for a given role:

rem ———————————————————————–
rem Filename: crate_like.sql
rem Purpose: Script to create a new user (with privs) like an existing
rem database user.
rem Date: 26 February 2013
rem Author: David Fitzjarrell
rem ———————————————————————–

set pages 0 feed off veri off lines 500

accept oldname prompt “Enter user to model new user to: “
accept newname prompt “Enter new user name: “

column password new_value psw noprint

select password
from user$
where name = upper(‘&&oldname’);

spool create_&&newname..sql
— Create user…
select ‘create user &&newname identified by values ”&&psw”’||
‘ default tablespace ‘||default_tablespace||
‘ temporary tablespace ‘||temporary_tablespace||’ profile ‘||
profile||’;’
from sys.dba_users
where username = upper(‘&&oldname’);

— Grant Roles…
select ‘grant ‘||granted_role||’ to &&newname’||
decode(ADMIN_OPTION, ‘YES’, ‘ WITH ADMIN OPTION’)||’;’
from sys.dba_role_privs
where grantee = upper(‘&&oldname’);

— Grant System Privs…
select ‘grant ‘||privilege||’ to &&newname’||
decode(ADMIN_OPTION, ‘YES’, ‘ WITH ADMIN OPTION’)||’;’
from sys.dba_sys_privs
where grantee = upper(‘&&oldname’);

— Grant Table Privs…
select ‘grant ‘||privilege||’ on ‘||owner||’.’||table_name||’ to &&newname;’
from sys.dba_tab_privs
where grantee = upper(‘&&oldname’);

— Grant Column Privs…
select ‘grant ‘||privilege||’ on ‘||owner||’.’||table_name||
‘(‘||column_name||’) to &&newname;’
from sys.dba_col_privs
where grantee = upper(‘&&oldname’);

— Set Default Role…
select ‘alter user &&newname default role all;’ from dual;
spool off

This will populate the username and the password hash, which should be replaced with parameters in the resulting script. (The values keyword should also be removed so an actual password can be provided.) Do this for one user in each privilege group, rename the script for role assigned and, voila, there is now an easy and efficient way to replicate permission levels for any user needing them. When done properly the script can be executed and it will prompt for the required values, allowing the DBA to supply the username and password. It will, of course, be necessary to document these values, at least temporarily, so that the affected user can be provided login information. As a final step the DBA should require the user to change passwords after the initial login. It is left to the DBA to decide whether or not to modify the code above to expire the password, forcing a password change.

Given the wide range of users a database may have using the role-based method of assigning privileges makes it easier and more reliable to grant these to a user account. Being consistent in granting access rights ensures all users with a given level of access have exactly the same set of grants. And grouping privileges by position makes the DBA’s job easier, especially if the role/script is named for the position to which these grants apply. Get the request, match the script, create the user. The only variable is making certain the login credentials work as expected. What could be easier?

Having someone ELSE do it, honestly.