Privileges and Role Authorization – Part 3

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 3

Managing User Roles:

Creating a Role

You use the “CREATE ROLE” statement to create a role.  You will need the “CREATE ROLE” system privilege to execute this command.  This system privilege is usually limited to security administrators.

Immediately after creating the role, it has no privileges associated with it.  You will continue by granting privileges and other roles to this role.

Roles are not contained in any schema, so the role name is database wide.  Oracle recommends that a role name, should contain at least one single byte character, if the database is using a multibyte character set.  The reason for this is that if the role name contains only multibyte characters, then the encrypted role name and password combination is less secure; as compared to having single byte characters mixed in.

Creating a Role identified by a password:

 A password protected role provides a measure of security.  For example a user can log in and grant a certain role to him/her self.  They will then have access to everything that has been granted in that role.  If the role has a password associated with it, then you have closed this particular security hole.  A role can be created as “NOT IDENTIFIED” or as “IDENTIFIED BY”.  A Role can be protected by a password in the database.  A Role can be authorized by an application, which invokes a specific package.  A Role can be authorised by the Operating system or network.  A role can be authorized Globally by an enterprise directory service.

For example:

SQL> create role fred identified by fred123;   

You can change the way a role is authorized after its creation by using the

SQL> ALTER ROLE …

Syntax.

Secure application roles and password protected roles can only be granted to users, and not to other roles

Of course you could also have opted to alter the way the role is authorized, for example:

SQL>  ALTER ROLE fred IDENTIFIED EXTERNALLY;

In order to be able to alter the authorization method for a role, you must have the “ALTER ANY ROLE” system privilege.  Alternatively if you were granted the role with the ADMIN option you can also change the authorization method.  So be careful about granting roles to users with the ADMIN option.

Specifying the Type of Role Authorization:

Remember that a role must be enabled before you can use it.  There are different ways to authorize a Role.

Authorise a Role by using the Oracle Database:

To do this you just need to assign a password to the role.  Now let’s say that a user has been granted a Role that is protected by a password.  You can’t grant a password protected role to another role.

You can then enable or disable the role for the user by using the “SET ROLE” statement.  For example: SQL> SET ROLE fred IDENTIFIED by <password>; This needs to be done explicitly.

If you are using a multibyte character set in your database, remember that you must use only single byte characters for the password, because multibyte characters are not currently supported in passwords.

Authorizing a Role by Using an Application:

The security design may have included an “application role”, or a “secure application role”.  The secure application role can only be enabled by an authorized PL/SQL package.

SQL> CREATE ROLE fred IDENTIFIED USING <package_name>;

This means that application developers do not need to embed passwords inside their applications.  All the application developer needs to do is to create an application role and specify which PL/SQL package will authorize the role.  The application role can be enabled by any module that is defined within the specified PL/SQL package.

For example:

SQL> CREATE ROLE fred_admin IDENTIFIED USING jim.admin;

Authorizing a Role by Using an External Source:

You can define the external role locally in the database.  You can authorize the role with the operating system or with a network client. 

However you can’t grant the external role to global users, or global roles, or other roles.  You can only grant the external role to a specific user.

For example:

SQL> CREATE ROLE freds_accounts IDENTIFIED EXTERNALLY;

·         Authorizing a Role by using the Operating system:  This is an Operating system dependent operation.  You need to configure information for each user at the operating system level.  You can only use operating system Role authentication when the operating system is able to dynamically link operating system privileges with applications.  For example when a user starts an application, the operating system grants an operating system privilege to the user.  The system privilege from the operating system, matches or corresponds to the appropriate role that is associated with the application.  At this point in time the application enables the application role.  When the application is ended or terminated by the user, then the operating system privilege is again revoked from the user account.

·         Authorizing a Role by Using a Network Client: By default the initialization parameter REMOTE_OS_ROLES is set to FALSE.  This means that by default if a user connects to the database over Oracle Net, then the operating system cannot authenticate their roles.  This default behaviour protects the database from users who may be impersonating a registered user over the network connection.  If you are not concerned with this security risk, and want to use operating system role authentication for network clients then: set the initialization parameter. REMOTE_OS_ROLES=TRUE

Global Role Authorization by an Enterprise Directory Service:

You can define a role as a “Global” role.  To do this you define the role in the database and grant other roles and privileges to it.  You cannot grant such a global role to any other role or user in the database.

The Global role is granted to a user when they connect to the database through an Enterprise Directory Service.  The global roles associated with the user are assigned at this point in the connection process.

SQL> CREATE ROLE fred_super IDENTIFIED GLOBALLY;

·         Global roles are a component of enterprise user security

·         Global roles apply to one database only

·         Enterprise role is defined in the enterprise directory.  An enterprise role consists of a directory structure that contains global roles from multiple databases.  An enterprise role can be granted to enterprise users

Granting and Revoking Roles:

A role consists of system and/or object privileges that have been granted to that role.  A role can be granted to any database user or to another role, provided that there are no specific restrictions in this regard.

A role can’t be granted to itself.  A role can’t be granted in a circular configuration, for example:  Role A can’t be granted to Role B, if Role B has already been granted to Role A.

The Oracle Database Server permits applications and users to enable and to disable roles.  Each role that is granted, will be either enabled or disabled.  The security domain of a user consists of all the privileges of all the roles that are currently enabled for a user.  The security domain of a user excludes all the privileges of any roles that are currently disabled for that user.

Indirectly granted roles are roles that have been granted to a role, that has been granted to a user.  When you enable or disable a role for a user, you in effect enable or disable all the indirectly granted roles for that user.

There are two ways to grant and revoke roles from users or other roles.  Firstly you can use Oracle Enterprise Manager Database Control, Secondly you can use the GRANT and REVOKE statements in SQL*Plus.  To grant and revoke privileges from a role, you would use the same method.

Users who can Grant and/or Revoke Roles:

You need the “GRANT ANY ROLE” system privilege, to be able to grant or revoke any role to other users or roles in the database, with the exception of Global Roles; because global roles are managed in Oracle Internet Directory.  By default the SYS and SYSTEM users have this privilege.  Be careful about granting this privilege to other users, because it is a very powerful privilege.  Any user who has been granted a role with the “ADMIN” option, can grant and revoke that role to other users and roles in the database.  The ADMIN option effectively creates selective administrative powers for specific roles, which is more secure than granting the “GRANT ANY ROLE” system privilege.

Dropping Roles:

Dropping a role automatically removes it from all user default role lists.  All indirectly granted roles are also removed from user default role lists.  The security domains of all users and roles that were granted a dropped role whether directly or indirectly, are immediately changed to reflect the absence of the dropped role/s.

Of course objects that are referenced in a role, may appear to have disappeared when the role is dropped or revoked.  In fact the objects are independent of the grants on the object, so the objects continue to exist.  Access or privileges on the specific objects are potentially lost, when the role is dropped.

In order to drop a role you will need the “DROP ANY ROLE” system privilege, or have been granted the role with the ADMIN option. For example:

SQL> DROP ROLE fred;

Restricting SQL*Plus Users from Using Database Roles:

SQL*Plus users and users of other ad-hoc tools, can present serious security problems which are multiplied when these users are able to use database roles.

Potential Security Problems of Using Ad Hoc tools

Database applications can explicitly control the actions of a user, and enable and disable the user roles for that user while the application runs.  On the other hand SQL*Plus and other ad hoc tools permit a user to execute whatever SQL statement they have permission to execute.  For example roles can be enabled and disabled from an ad hoc tool.

A user operating an ad hoc tool has the potential to issue destructive SQL statements against the database tables and other objects in the database.

For example:

Let’s say that the accounts receivable application has an accounts_receivable role, which is granted to users of the application.  This role includes the privileges needed to execute selects, inserts, updates and deletes against the financial tables in the application.  When accessing the data through the application, the application controls how things are done, and the specific operations that are allowed according to a defined set of rules.

Now let’s suppose that a user who has been granted the “accounts_receivable” role logs into the database via an ad hoc tool like SQL*Plus.  Now this user has all the privileges that are granted through the accounts_receivable role.  This user can select data, update data, insert data, and even delete data.  However because the access is made through an ad hoc tool, none of the defined set of rules is applied to the actions that the user can carry out.  The user can modify data, insert data, and delete data, and then query the data to see the results of the action.

Limiting Roles Through the PRODUCT_USER_PROFILE table

You can use the object SYSTEM.PRODUCT_USER_PROFILE to limit commands in the SQL*Plus environment for individual users. You can also restrict access to the GRANT, REVOKE, and SET ROLE commands to control the user’s ability to change their database privileges in a SQL*Plus environment.

You could for example create an entry in the system.product_user_profile object, to prevent user fred from using the “HOST” command while in SQL*Plus.

As the system user:

SQL> INSERT INTO system.product_user_profile

(product, userid, attribute, scope, numeric_value, char_value, date_value, long_value)

VALUES

('SQL*Plus', 'FRED', 'HOST', NULL, NULL, 'DISABLED', NULL, NULL);

SQL> commit;

To show that user fred has effectively had the HOST command disabled in the SQL*Plus sessions:

SQL> connect fred/<password>

Connected.

SQL> host

SP2-0544: Command "host" disabled in Product User Profile

To start setting up this functionality, you must run the following command as SYSTEM, if it has not been previously run.

SQL> {$ORACLE_HOME}/sqlplus/admin/pupbld.sql

This will create the following data dictionary objects for you.

Product_profile, Product_user_profile

SQL> l

  1  select owner, SYNONYM_NAME,TABLE_OWNER,TABLE_NAME

  2* from dba_synonyms where SYNONYM_NAME in ('PRODUCT_PROFILE','PRODUCT_USER_PROFILE')

SQL> /

OWNER      SYNONYM_NAME           TABLE_OWNE TABLE_NAME

---------- ---------------------- ---------- ------------------------------

SYSTEM     PRODUCT_USER_PROFILE   SYSTEM     SQLPLUS_PRODUCT_PROFILE

PUBLIC     PRODUCT_PROFILE        SYSTEM     PRODUCT_PRIVS

PUBLIC     PRODUCT_USER_PROFILE   SYSTEM     PRODUCT_PRIVS

There are a number of SQL*Plus commands that can be disabled using this functionality:

COPY, DECLARE, EDIT, EXECUTE, EXIT, GET, HOST, PASSWORD, QUIT, RUN, SAVE, SET, SPOOL, START

Then there are a number of straight forward SQL commands that can be disabled using this functionality:

ALTER, ANALYZE, AUDIT, CONNECT, CREATE, DELETE, DROP, GRANT, INSERT, LOCK, NOAUDIT, RENAME, REVOKE, SELECT, SET CONSTRAINTS, SET ROLE, SET TRANSACTION, TRUNCATE, UPDATE

To disable a SQL or SQL*Plus command for the user ‘FRED’,  insert a row containing the user’s username in the USERID column, the command name in the ATTRIBUTE column, and ‘DISABLED’ in the CHAR_VALUE column.

Insert into system.product_user_profile

(product, userid, attribute, scope, numeric_value, char_value, date_value, long_value)

Values

(‘SQL*Plus’,’FRED’,’DELETE’,NULL,NULL,’DISABLED’,’NULL,NULL);

Commit;

When you give the USERID field the value of ‘PUBLIC’, or ‘%’, then the command will be effective for all users.  For example if you want to prevent all users from spooling from the SQL*Plus prompt:

Insert into system.product_user_profile

(product, userid, attribute, scope, numeric_value, char_value, date_value, long_value)

Values

(’SQL*Plus’,’%’,’SPOOL’,NULL,NULL,’DISABLED’,NULL,NULL);

SQL> spool fred.txt

SP2-0606: Cannot create SPOOL file "fred.txt"

To re-enable commands you simply delete the row containing the restriction.

Using Stored Procedures to Encapsulate Business Logic

Stored procedures encapsulate the privileges that are needed, together with the business logic.  In this way the privileges are only exercised in the context of a properly controlled business transaction.

The use of the procedure can be limited to normal business hours, in order to prevent people from making changes on the system outside of business hours.  Update privileges on a table can be granted on the procedure instead of to the user.  In this way the table can only be updated, if accessed in the designed way through the procedure.  This deny the user the possibility of accessing the table and doing updates through an ad hoc tool such as SQL*Plus.

Securing Role Privileges by Using Secure Application Roles:

A secure application role can only be enabled by an authorized PL/SQL package or procedure.  The PL/SQL package reflects the privileges and security policies needed to control access to the application.

This arrangement limits or restricts the enabling of this type of role to the invoking application.  The application can verify within itself before executing the package, whether the user has connected through a proxy, and whatever other validation is needed.

A secure application role strengthens the system security, because you do not need to embed passwords in the application or in a table for that matter.  Using this method the application is limited to actions that are defined in the security policies.  These definitions are stored in the application in one place.  You can modify security policies in one place without having to recompile the application.

To enable the secure application role, you must execute its underlying package, by invoking it directly from the application once the user has logged in.  It needs to be executed before the user exercises the privileges that have been granted to the secure application role.  You can’t use a logon trigger to enable a secure application role.  A secure application role cannot be a default role.

When you enable the secure application role from the application, Oracle Database Server verifies that the authorized PL/SQL package is on the calling stack.  In other words, Oracle Database verifies that the authorized PL/SQL package is issuing the command to enable the role, and not some other process.

You can verify that there is a database connection associated with the user that is trying to authorize the role within the package, this can prevent unauthorized processes from within the Oracle Database Server from invoking the role.

Because the role is implemented by a package, the package can execute additional steps like looking for a specific IP range, or validating that the user connected from a specific middle tier.  Users are ultimately forced to work within the framework of the application.  The same user would not have access to the privileges if that user were to be outside of the application.

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.