Privileges and Role Authorization – Part 1

Dear Readers,

My name is Franz Devantier, creator of this blog.  I am an Oracle Certified Professional (OCP DBA 11g) Security DBA.  I will be sharing with you the basic duties of an Oracle DBA, and also some of the undocumented, and not so well known tasks. 

Privileges and Role Authorization – Part 1

A Role is a named group of related privileges and other roles that you can grant to users or other roles.  A default role is automatically enabled for a user, when a user creates a session.

When you create a new user with the “CREATE USER” statement, you can’t set a default role for the user.  A user can be assigned zero or more default roles.  Typically a user would need the “CONNECT” role or the create session privilege, in order to be able to connect to the database.

A new user has the default role setting of “ALL”.  This means that all roles subsequently granted to the user will be default roles.  To change the default role for a user:

SQL> grant user fred fred_role;

SQL> alter user fred DEFAULT ROLE fred_role;

Notice that before a role can be made the default role for a user, the user must already be granted that role.

The two primary processes of Authorization

·         Permitting users to access, process, and alter data

·         Applying limits on user access and/or actions

Limitations can be placed on or removed from users.  Users can be limited in terms of access to schemas, tables, rows, or to resources such as CPU time, connect time, or Idle time.

You can think of a user privilege, as the right to access an object that belongs to another user.  The right to run particular types of SQL statements.  The right to execute a PL/SQL package.  The right to the privileges that are defined by the Oracle Database Server.

Roles can be system generated / created, or created by administrative users, or other users with the privileges to do so.  A Role enables the granting of a set of multiple privileges and roles to a user with one grant statement.

System Privileges: These privileges allow the grantee to perform administrator type tasks in the database.  These system privileges should be limited to trusted administrative users only.  For example the “DBA” role which contains the system privileges, should never be granted to an application user.

User Roles:  A role groups several privileges and roles together, so that they can be granted and revoked from users in one action.  Keep in mind that the user role must be enabled for the user before the user can see it.

Object Privileges:  Objects can have different types of privileges assigned to them; like insert, update, delete, execute etc.

Granting Privileges to appropriate Users:  Typically you will grant privileges to users, so that they can accomplish the tasks required for their job functions.  If an application user wants you to grant them the “DBA” role, then remember that this role contains privileges that are not appropriate for such a user.  By granting excessive or unnecessary privileges you compromise the security of the system.  Privileges contained in roles like “SYSDBA”, or “SYSOPER”, or “DBA”, are only appropriate for users who perform administrative tasks.

How users receive privileges:  You can grant the individual privileges to a user that needs a certain set of privileges.  However for the next user with the same requirement, you will have to perform the entire process again.  So the better approach is to grant all the needed privileges to a role; then you can grant this role to all the users who need the defined set of privileges.

Roles allow for better and easier management of privileges.  So best practice is to grant a set of privileges to one or more roles, and then grant these roles to the specific users.

Managing System Privileges

What is a system privilege

A system privilege is the right to perform a specified action on any schema object of a certain type.  Creating tablespaces and deleting the rows from any table, are system privileges.  There are over 100 system privileges.  Each system privilege allows a trusted user to perform a specific database operation.  System privileges are very powerful, and should be limited to a few trusted administrators, like Database Administrators. 

Some examples of powerful system privileges:

System Privilege	Operations Permitted
ANALYZE ANY	Analyze any table, cluster, or index in the database.
AUDIT ANY	Audit any schema object in the database.
AUDIT SYSTEM	Enable and disable statement and privilege audit options.
CREATE ANY CLUSTER	Create a cluster in any schema. Behaves similarly to CREATE ANY TABLE.
ALTER ANY CLUSTER	Alter any cluster in the database.
DROP ANY CLUSTER	Drop any cluster in the database.
ALTER DATABASE	Alter the database; add files to the operating system via Oracle, regardless of operating system privileges.
CREATE DATABASE LINK	Create private database links in own schema.
CREATE ANY INDEX	Create an index in any schema on any table.
ALTER ANY INDEX	Alter any index in the database.
DROP ANY INDEX	Drop any index in the database.
CREATE ANY LIBRARY	Create callout libraries in any schema
DROP ANY LIBRARY	Drop callout libraries in any schema.
GRANT ANY PRIVILEGE	Grant any system privilege (not object privileges).
CREATE ANY PROCEDURE	Create stored procedures, functions, and packages in any schema. (Requires that user also have ALTER ANY TABLE, BACKUP ANY TABLE, DROP ANY TABLE, SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE, or GRANT ANY TABLE privilege.)
ALTER ANY PROCEDURE	Compile any stored procedure, function, or package in any schema.
DROP ANY PROCEDURE	Drop any stored procedure, function, or package in any schema.
EXECUTE ANY PROCEDURE	Execute any procedure or function (stand-alone or packaged), or reference any public package variable in any schema.
CREATE PROFILE	Create profiles.
ALTER PROFILE	Alter any profile in the database.
DROP PROFILE	Drop any profile in the database.
ALTER RESOURCE COST	Set costs for resources used in all user sessions.
CREATE PUBLIC DATABASE LINK	Create public database links.
DROP PUBLIC DATABASE LINK	Drop public database links.
CREATE PUBLIC SYNONYM	Create public synonyms.
DROP PUBLIC SYNONYM	Drop public synonyms.
CREATE ROLE	Create roles.
ALTER ANY ROLE	Alter any role in the database.
DROP ANY ROLE	Drop any role in the database.
GRANT ANY ROLE	Grant any role in the database.
CREATE ROLLBACK SEGMENT	Create rollback segments.
ALTER ROLLBACK SEGMENT	Alter rollback segments.
DROP ROLLBACK SEGMENT	Drop rollback segments.
ALTER SESSION	Issue ALTER SESSION statements.
RESTRICTED SESSION	Connect when the database has been started using STARTUP RESTRICT. (The OSOPER and OSDBA roles contain this privilege.)
CREATE SEQUENCE	Create a sequence in own schema.
CREATE ANY SEQUENCE	Create any sequence in any schema.
ALTER ANY SEQUENCE	Alter any sequence in any schema.
DROP ANY SEQUENCE	Drop any sequence in any schema.
SELECT ANY SEQUENCE	Reference any sequence in any schema.
CREATE SNAPSHOT	Create snapshots in any schema. (User must also have the CREATE ANY TABLE privilege.)
ALTER SNAPSHOT	Alter any snapshot in any schema.
DROP ANY SNAPSHOT	Drop any snapshot in any schema.
CREATE ANY SYNONYM	Create any synonym in any schema.
DROP ANY SYNONYM	Drop any synonym in any schema.
ALTER SYSTEM	Issue ALTER SYSTEM statements.
CREATE ANY TABLE	Create tables in any schema. (If grantee has CREATE ANY TABLE privilege and creates a table in another user's schema, the owner must have space quota on that tablespace. The table owner need not have the CREATE [ANY] TABLE privilege.)
ALTER ANY TABLE	Alter any table in any schema and compile any view in any schema.
BACKUP ANY TABLE	Perform an incremental export using the Export utility of tables in any schema.
DROP ANY TABLE	Drop or truncate any table in any schema.
LOCK ANY TABLE	Lock any table or view in any schema.
COMMENT ANY TABLE	Comment on any table, view, or column in schema.
SELECT ANY TABLE	Query any table, view, or snapshot in any schema.
INSERT ANY TABLE	Insert rows into any table or view in any schema.
UPDATE ANY TABLE	Update rows in any table or view in any schema.
DELETE ANY TABLE	Delete rows from any table or view in any schema.
CREATE TABLESPACE	Create tablespaces; add files to the operating system via Oracle, regardless of the user's operating system privileges.
ALTER TABLESPACE	Alter tablespaces; add files to the operating system via Oracle, regardless of the user's operating system privileges.
MANAGE TABLESPACE	Take any tablespace offline, bring any tablespace online, and begin and end backups of any tablespace.
DROP TABLESPACE	Drop tablespaces.
UNLIMITED TABLESPACE	Use an unlimited amount of any tablespace. This privilege overrides any specific quotas assigned. If revoked, the grantee's schema objects remain but further tablespace allocation is denied unless allowed by specific tablespace quotas. This system privilege can be granted only to users and not to roles. In general, specific tablespace quotas are assigned instead of granting this system privilege.
FORCE TRANSACTION	Force the commit or rollback of own in-doubt distributed transaction in the local database.
FORCE ANY TRANSACTION	Force the commit or rollback of any in-doubt distributed transaction in the local database.
CREATE ANY TRIGGER	Create any trigger in any schema associated with any table in any schema.
ALTER ANY TRIGGER	Enable, disable, or compile any trigger in any schema.
DROP ANY TRIGGER	Drop any trigger in any schema.
CREATE ANY USER	Create users; assign quotas on any tablespace, set default and temporary tablespaces, and assign a profile as part of a CREATE USER statement.
BECOME ANY USER	Become another user. (Required by any user performing a full database import.)
ALTER USER	Alter other users: change any user's password or authentication method, assign tablespace quotas, set default and temporary tablespaces, assign profiles and default roles, in an ALTER USER statement. (Not required to alter own password.)
DROP USER	Drop another user.
CREATE ANY VIEW	Create a view in any schema. To create a view in another user's schema, you must have CREATE ANY VIEW privileges, and the owner must have the required privileges on the objects referenced in the view.
DROP ANY VIEW	Drop any view in any schema.

Why should a system privilege be restricted

Because the system privileges are so powerful, the default database is configured in such a way as to prevent non-administrative users from executing the system privileges, especially the “ANY” system privileges.

The DBA should ensure that the data dictionary is protected by setting the “07_DICTIONARY_ACCESSIBILITY” initialization parameter to FALSE.  This is the default setting, and is also known as the “Dictionary Protection Mechanism”.

The “07_DICTIONARY_ACCESSIBILITY” parameter controls the restrictions on system privileges, when you upgrade from Oracle release 7 to 8i or later releases.  If this parameter is set to “TRUE”, then accessing objects by all users in the SYS schema is allowed.  This was the behaviour in Oracle release 7.  This behaviour is dangerous because the “ANY” privilege applies to the data dictionary.  As a result a non-administrative user could alter the data dictionary tables.   

Check the setting of this parameter.

SQL> select name, value from v$parameter where name like '%DICT%';

If the result of the field value is “TRUE” then:

Firstly you need to know if you are running with an spfile or not, before you can make any changes.  If you are not in spfile mode then.

SQL> create spfile from pfile;

Restart the database.

Then you can change the value to “FALSE”.

SQL> alter system set 07_DICTIONARY_ACCESSIBILITY=FALSE scope=both;

As soon as 07_DICTIONARY_ACCESSIBILLITY is set to FALSE, then users with the ANY privilege, are no longer allowed access to objects in the SYS schema.  Access to objects in the SYS schema or data dictionary objects is limited to users who can log in with the SYSDBA privilege.  If 07_DICTIONARY_ACCESSIBILITY is set to TRUE, then you would be able to log in as SYS without specifying “as sysdba / sysoper”.

System privileges that provide access to objects in other schemas, do not give other users access to the objects in the SYS schema.  For example, a user with the select any table system privilege, would be able to select from any table, but would not be able to select from a table in the data dictionary.  If required you can grant explicit object privileges to access objects in the SYS schema.

There are then two groups of users that can access objects in the SYS schema:

·         Those with administrative privileges, who connect as SYSDBA

·         Those with specific object privileges in the SYS schema granted to them

These are the roles that you can grant to users that need access to objects in the SYS schema.

Role	Notes
SELECT_CATALOG_ROLE	Allows users SELECT privileges on data dictionary views
EXECUTE_CATALOG_ROLE	Allows users EXECUTE privileges for packages and procedures in the data dictionay
DELETE_CATALOG_ROLE	Allows users to delete records from the system audit tables.  SYS.AUD$ and sys.FGA_LOG$

If you want to allow a user to access to any object in the SYS schema, including tables created in the SYS schema; then you can grant the user the “SELECT ANY DICTIONARY” system privilege.  This privilege must be granted individually to each user with the requirement.  This system privilege is not included in the “GRANT ALL PRIVILEGE”.  This privilege can be granted through a role.

The above three roles, and the “SELECT ANY DICTIONARY” privilege should be granted with extreme care, because the integrity of your system can be compromised if they are misused.

You can grant and revoke system privileges to roles and users.  When granting system privileges to users, ensure that you maintain the separation of duties.

You can grant or revoke system privileges using SQL*Plus or from Oracle Enterprise Manager Database Control.

There are two types of users who can grant system privileges to other users, so you must be very careful in enabling them:

·         Users with the system privilege “GRANT ANY PRIVILEGE”

·         Users who were granted a specific system privilege with the “ADMIN OPTION”

The “ANY” privilege and the “PUBLIC” role

A system privilege with the “ANY” keyword enables you to set privileges for an entire category of objects in the database.  The CREATE ANY PROCEDURE system privilege will allow the user to create a procedure anywhere in the database.

For example if user A creates a procedure in user B’s schema, then the procedure will run as user B.  If user B has DBA privileges, or privileges to which user A has no access; then a security violation has been created.

The “PUBLIC” role is a special role that is created with the database.  All user accounts assume the PUBLIC roles, besides whatever other Roles have been granted to them.  The PUBLIC role does not appear in the DBA_ROLES and SESSION_ROLES data dictionary views.  You can’t drop the PUBLIC role.

You can grant privileges to the PUBLIC role.   Privileges granted to the public role make the privilege available to every user in the database.  Think carefully before granting anything to PUBLIC.  For example if user fred is granted the “CREATE PUBLIC SYNONYM” privilege.  User fred could then redefine an utility or interface that most people are using.  Instead of going to the real utility, they would be redirected to user fred’s utility.  User fred may be using the interface to steal login credentials or redirect financial information etc.

So to summarize: be very careful when granting privileges using the ANY or PUBLIC keywords.  Always grant the least privileges, and rather go through a series of adding additional privileges, instead of granting more than the user needs.  Make sure that the initialization parameter “07_DICTIONARY_ACCESSIBILITY” is set to FALSE.

Franz Devantier,

Need a database health check?

devantierf@gmail.com

Income stabilizer (win-win opportunity)

Join the carefully selected and tested cash-flow generating program below to potentially create a long-term residual or annuity type income enhancer for yourself.

Traffic Wave - Free Report:  The report will give you all the information you need to start making a nice long-term residual income stream for yourself.