Blog - comments

When I studied Oracle New Feature Guide "Media Failure: PDB SYSTEM Data File" , I was surprised tha...
Hayat Khan

Really a nice article to study.

Thanks,

Amol Bhoite

ana 100 goid

mokok

ana 100 gold

mokok

Bonjour,

Tout d'abord merci pour cet article. J'aimerai savoir si ACFS est gratuit ?

Chris

Chris
Blog Gregory Steulet Oracle Database 12c new features: segregation of duties with new administrative roles

dbi services Blog

Welcome to the dbi services Blog! This blog focuses on IT infrastructure - featuring news, troubleshooting, and tips & tricks. It covers database, middleware, and OS technologies such as Oracle, Microsoft SQL Server, Documentum, MySQL, PostgreSQL, Sybase, Unix/Linux, etc. The dbi services blog represents the view of our consultants, not necessarily that of dbi services. Feel free to comment on the postings!

  • Home
    Home This is where you can find all the blog posts throughout the site.
  • Categories
    Categories Displays a list of categories from this blog.
  • Tags
    Tags Displays a list of tags that have been used in the blog.
  • Bloggers
    Bloggers Search for your favorite blogger from this site.

Oracle Database 12c new features: segregation of duties with new administrative roles

Starting with Oracle Database 12c Release 1, Oracle has introduced a set of more task-oriented new administrative privileges. Prior to Oracle 12c you had to have the SYSDBA privilege to make a backup, monitor the Data Guard status, or administrate certain features of Oracle Transparent Data Encryption. With Oracle 12c Release 1, this is not the case anymore. You can now grant limited administrative privileges for the operating of a Oracle 12c database.

This new feature can already be seen during the Oracle 12c installation process. Indeed, during the binary installation, Oracle asks for the granting of privileges to the following groups:

 

Oracle 12.1 Administrative Roles during Installation

Despite the fact that we still have the possibility to provide only one group for each of these entries, Oracle recommends to provide a unique group to designate each privilege.

Among these new administrative privileges, one can find the following:

OS Group Database Privilege Short description
backupdba SYSBACKUP This group allows operating system users to have a limited set of database backup and recovery related administrative privileges
dgdba SYSDG This group allows a group of operating sytem users to have a limited set of privileges to administer and monitor Oracle Data Guard
kmdba SYSKM This allows operating sytem users to have a limited set of privileges for encryption key management such as Oracle Wallet Manager management, since, by default, Oracle 12c encapsulates features such as database vault


Of course, in order to grant these privileges to users you will have to know exactly what is behind these new administrative privileges. You can find the operations allowed for each of these administrative privileges below:

 

SYSBACKUP

  • STARTUP

  • SHUTDOWN

  • ALTER DATABASE

  • ALTER SYSTEM

  • ALTER SESSION

  • ALTER TABLESPACE

  • CREATE CONTROLFILE

  • CREATE ANY DIRECTORY

  • CREATE ANY TABLE

  • CREATE ANY CLUSTER

  • CREATE PFILE

  • CREATE RESTORE POINT (including GUARANTEED restore points)

  • CREATE SESSION

  • CREATE SPFILE

  • DROP DATABASE

  • DROP TABLESPACE

  • DROP RESTORE POINT (including GUARANTEED restore points)

  • FLASHBACK DATABASE

  • RESUMABLE

  • UNLIMITED TABLESPACE

  • SELECT ANY DICTIONARY

  • SELECT ANY TRANSACTION

  • SELECT

    • X$ tables (that is, the fixed tables)

    • V$ and GV$ views (that is, the dynamic performance views)

    • APPQOSSYS.WLM_CLASSIFIER_PLAN

    • SYSTEM.LOGSTDBY$PARAMETERS

  • DELETE/INSERT

    • SYS.APPLY$_SOURCE_SCHEMA

    • SYSTEM.LOGSTDBY$PARAMETERS

  • EXECUTE

    • SYS.DBMS_BACKUP_RESTORE

    • SYS.DBMS_RCVMAN

    • SYS.DBMS_DATAPUMP

    • SYS.DBMS_IR

    • SYS.DBMS_PIPE

    • SYS.SYS_ERROR

    • SYS.DBMS_TTS

    • SYS.DBMS_TDB

    • SYS.DBMS_PLUGTS

    • SYS.DBMS_PLUGTSP

  • SELECT_CATALOG_ROLE

 

SYSDG

  • STARTUP

  • SHUTDOWN

  • ALTER DATABASE

  • ALTER SESSION

  • ALTER SYSTEM

  • CREATE RESTORE POINT (including GUARANTEED restore points)

  • CREATE SESSION

  • DROP RESTORE POINT (including GUARANTEED restore points)

  • FLASHBACK DATABASE

  • SELECT ANY DICTIONARY

  • SELECT

    • X$ tables (that is, the fixed tables)

    • V$ and GV$ views (that is, the dynamic performance views)

    • APPQOSSYS.WLM_CLASSIFIER_PLAN

  • DELETE

    • APPQOSSYS.WLM_CLASSIFIER_PLAN

  • EXECUTE

    • SYS.DBMS_DRS

 

SYSKM

  • ADMINISTER KEY MANAGEMENT

  • CREATE SESSION

  • SELECT (only when database is open)

    • SYS.V$ENCRYPTED_TABLESPACES

    • SYS.V$ENCRYPTION_WALLET

    • SYS.V$WALLET

    • SYS.V$ENCRYPTION_KEYS

    • SYS.V$CLIENT_SECRETS

    • SYS.DBA_ENCRYPTION_KEY_USAGE

 

Tests

In order to illustrate the usage of this new feature, let’s connect with an operating system user who is member of the backupdba operating system group and change the sga_target parameter:

 
oracle@vmtestoel01:/home/oracle/ [SOUK] id grs
uid=54323(grs) gid=54324(backupdba) groups=54324(backupdba)


oracle@vmtestoel01:/home/oracle/ [SOUK] su - grs

grs@vmtestoel01:/home/grs/ [SOUK]  sqlplus / as sysbackup

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit

SQL> alter system set sga_target=800M;

System altered.

 

Now, let's try to drop a tablespace with a user (in our case "her") who is a member of the dgdba operating system group (SYSDG). This user should not be allowed to perform such an operation since the privilege "drop tablespace" is not in the SYSDG group.

 

[root@vmtestoel01 ~]# id her
uid=54324(her) gid=54325(dgdba) groups=54325
(dgdba)

 

oracle@vmtestoel01:/home/oracle/ [SOUK] su - her

 

her@vmtestoel01:/home/her/ [SOUK]  sqlplus /as sysdg

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit

SQL> drop tablespace temp including contents and datafiles;

drop tablespace temp including contents and datafiles
*
ERROR at line 1:
ORA-01031: insufficient privileges

 

As expected, the user "her" was not allowed to drop a tablespace, since SYSDG is not allowed to perform such an operation.

 

Conclusion

I think this new feature will be very valuable and appreciated especially in an environment where a strong segregation of duties is made. However, the usage of this new feature is not mandatory since Oracle allows to use the same OS group (for instance dba) for each administrative group.

Have fun with Oracle 12c Release 1!

Rate this blog entry:
2

Grégory Steulet  is Chief Financial Officer (CFO) and Region Manager at dbi services. He has more than ten years of experience in database and infrastructure management, engineering, and optimization. He is specialized in Oracle technologies and high availability solutions (Oracle DataGuard, Data Replication Block Device). His expertise also includes Avaloq banking applications, as well as the open source field (MySQL, Unix/Linux, etc.). Grégory Steulet is "Oracle Certified Professional 10g", "MySQL Cluster 5.1 Certified", and "Avaloq Certified Professional 2.6". Prior to joining dbi services, Grégory Steulet was Senior Consultant at Trivadis in Lausanne. He also worked as IT Administrator at Box Telecom in Miami Beach, Florida (USA). Grégory Steulet has an Executive MBA from the International Institute of Management in Technology, Fribourg (CH). He also holds a Bachelor's Degree in Business Administration and Computer Science from the University of Applied Sciences Western Switzerland. His branch-related experience covers Telecommunications, Financial Services / Banking, Logistics, Pharma etc.

Comments

  • No comments made yet. Be the first to submit a comment

Leave your comment

Guest Sunday, 20 April 2014
AddThis Social Bookmark Button
Deutsch (DE-CH-AT)   French (Fr)
NewsOfficesContact

Contact

Contact us now!

Send us your request!

Our workshops

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

Expert insight from insiders!

Fixed Price Services

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

A safe investment: our IT services at fixed prices!

Your flexible SLA

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

ISO 20000 certified & freely customizable!

dbi services Newsletter