Database Support: Security, Addressing the Connect Role Change

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.

Security, Addressing the Connect Role Change - Part 2

The “CONNECT” role was introduced in Oracle Database version 7. The CONNECT role originally had the following privileges granted to it:

· Alter Session

· Create Session

· Create Cluster

· Create Synonym

· Create Database Link

· Create table

· Create Sequence

· Create View

Starting from Oracle Database 10g release 2 or 10.2, the CONNECT role only had one privilege granted to it:

· Create Session

Addressing the limited CONNECT role in 10.2

Solution 1

The easiest way to manage the missing privileges from the CONNECT role, is to create a custom database role, and grant that role to the user.

For example:

SQL> create role custom_role_001;

Role created.

SQL> GRANT CREATE TABLE,

2 CREATE VIEW,

3 CREATE SEQUENCE,

4 CREATE SYNONYM,

5 CREATE CLUSTER,

6 CREATE DATABASE LINK

7 TO custom_role_001;

Grant succeeded.

Now you can determine which users you are going to grant the role to. If you have just upgraded from a previous version to 10.2, then you can determine which users have the SESSION role, and then grant those users the new role. (custom_role_001).

SQL> SELECT user$.name, admin_option, default_role

2 FROM user$,

3 sysauth$,

4 dba_role_privs

5 WHERE privilege# = (SELECT user#

6 from user$

7 WHERE name = 'CONNECT')

8 AND user$.user# = grantee#

9 AND grantee = user$.name

10 AND granted_role = 'CONNECT'

11 AND user$.name NOT IN ('SYS','SYSTEM','MDSYS','WMSYS','WBSYS');

NAME ADM DEF

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

FRED NO YES

MDDATA NO YES

SPATIAL_CSW_ADMIN_USR NO YES

SCOTT NO YES

APEX_030200 YES YES

SPATIAL_WFS_ADMIN_USR NO YES

OWBSYS YES YES

JIM NO YES

8 rows selected.

SQL> grant custom_role_001 to FRED;

Grant succeeded.

You can now take it a step further, and determine which privileges are required by which users, by using Oracle Auditing. You can after a period of operation analyze this Audit information to establish which additional database roles could be created. These additional roles can add some additions, and limit or be more specific, as to exactly what the users can do. In this way you would be providing a finer granularity, with the granted privileges.

Verify that the database initialization parameter “AUDIT_TRAIL” is set.

SQL> show parameter AUDIT_TRAIL

NAME TYPE VALUE

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

audit_trail string DB

Now you can audit the specific privileges that would have been in the CONNECT role.

SQL> AUDIT CREATE TABLE, CREATE SEQUENCE, CREATE SYNONYM, CREATE DATABASE LINK, CREATE CLUSTER, CREATE VIEW, ALTER SESSION;

Audit succeeded.

Now you can do a quick check on exactly which privileges are being used.

SQL> SELECT userid, name

2 FROM aud$,

3 system_privilege_map

4 WHERE - priv$used = privilege

5 AND userid not in ('DBSNMP','SYSMAN','SYS','SYSTEM');

USERID NAME

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

FRED CREATE TABLE

FRED CREATE SESSION

JIM CREATE SESSION

JIM CREATE TABLE

JIM CREATE SYNONYM

As a result of this research you may decide to create a custom role in place of the custom_role_001 custom role, that has a more limited set of privileges granted to it.

Solution 2

Starting with 10.2, Oracle provides a script called “rstrconn.sql”, located in the $ORACLE_HOME/rdbms/admin directory. If you are having issues, then this is a quick way to restore the grants that were present in versions of the Oracle database server, prior to 10.2.

D:\app\product\11.2.0\dbhome_1\RDBMS\ADMIN>type rstrconn.sql

Rem

Rem $Header: rstrconn.sql 10-aug-2004.14:24:52 pthornto Exp $

Rem

Rem rstrconn.sql

Rem

Rem NAME

Rem rstrconn.sql - SQL*Plus script to grant all the

Rem pre-10gR2 privielges back to CONNECT Role.

Rem

Rem DESCRIPTION

Rem This script should be run by a user who is a SYSDBA or has the

Rem DBA role granted to them.

Rem

Rem NOTES

Rem By default, 10gR2 and higher only grants CREATE SESSION

Rem to CONNECT. This script can be used to restore

Rem pre-10GR2 CONNECT privileges

Rem

Rem MODIFIED (MM/DD/YY)

Rem pthornto 08/10/04 - pthornto_sqlbsq_connect_deprecate

Rem pthornto 08/10/04 - Created

Rem

GRANT create session, create table, create view, create synonym,

create database link, create cluster, create sequence, alter session

TO CONNECT;

commit;

If you are following this solution to get your application up and running again, after an upgrade, then you should make sure that you can revoke privileges from users, who do not need them.

SQL> show parameter AUDIT_TRAIL

NAME TYPE VALUE

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

audit_trail string DB

SQL> AUDIT CREATE TABLE, CREATE SEQUENCE, CREATE SYNONYM, CREATE DATABASE LINK, CREATE CLUSTER, CREATE VIEW, ALTER SESSION;

Audit succeeded.

Then you are in a position to analyze the audit data after a period of time, to decide which privileges can be revoked.

SQL> SELECT userid, name

2 FROM aud$,

3 system_privilege_map

4 WHERE - priv$used = privilege

5 AND userid not in ('DBSNMP','SYSMAN','SYS','SYSTEM');

USERID NAME

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

FRED CREATE TABLE

FRED CREATE SESSION

JIM CREATE SESSION

USERID NAME

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

JIM CREATE SESSION

FRED CREATE SESSION

SCOTT CREATE SESSION

CTXSYS CREATE SESSION

In this case you can probably revoke some of the privileges from fred for example.

Here is a quicker way to see which users have been granted the CONNECT role.

SQL> select * from DBA_CONNECT_ROLE_GRANTEES;

GRANTEE PATH_OF_CONNECT_ROLE_GRANT ADM

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

APEX_030200 CONNECT/APEX_030200 YES

FRED CONNECT/FRED NO

JIM CONNECT/JIM NO

MDDATA CONNECT/MDDATA NO

MDSYS CONNECT/MDSYS NO

OWBSYS CONNECT/OWBSYS YES

SCOTT CONNECT/SCOTT NO

SPATIAL_CSW_ADMIN_USR CONNECT/SPATIAL_CSW_ADMIN_USR NO

SPATIAL_WFS_ADMIN_USR CONNECT/SPATIAL_WFS_ADMIN_USR NO

SYS CONNECT/SYS YES

WMSYS CONNECT/WMSYS NO

11 rows selected.

Solution 3

This is the approach that should be taken by default, unless there are reasons why the first two solutions would be a better fit.

Mitigating Risk, by limiting privileges to the minimum set required to perform a given function. This approach results in a more secure application.

You will need to do a substantial amount of analysis, to determine which users need which access. The users should them be put into groups, corresponding to the privileges that they require.

Each user group, will then have a special custom role created for it, with just those required grants, granted to the role. This role will then be granted to all the members of the group.

As the privilege needs evolve with the application, the roles can be adjusted, or individual grants given to individual users.

At any point in time, you can be sure that the privileges granted to the users have been limited to the essential privileges. This reduces the risk of damage to your data, either inadvertently or maliciously.

Franz Devantier,

Need a database health check, or a security audit?

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.

Database Support

Wednesday, March 6, 2013

Security, Addressing the Connect Role Change - Part 2

No comments:

Post a Comment