Thursday , September 28 2023

Critical Authority In Oracle Database

In today’s article, we will examine what we need to pay attention to when performing Critical Authority Controls in Oracle Database.

The most critical authorizations for database users are “ALTER” and “ANY” privileges.

Users who receive this authorization can also dominate over other users.

It can delete, recreate and edit all tables on the database.

This type of authorization is strongly discouraged. What is required is authorization according to need.

Oracle Database default users, for example ” SYS ” or ” SYSTEM, need these privileges.

The SQL query is given below, where you can find the people with critical privileges mentioned above, excluding such users.

When you run the above code block, you will get a dump like below.

To be clear, I have authorized the user “HR”.

We see it below. You should examine the users you come across in detail and get their authorization if necessary.

So how do you get these powers?

Syntax : REVOKE {system_privilege | object_privilege } FROM user;

Example ;

They may have received these authorizations with the additional feature we call “ADMIN OPTION”.

ADMIN OPTION is a feature that you can give to someone else the privileges given to you.

In other words, if you have been given the “CREATE ANY TABLE” authority with “ADMIN OPTION”, you can give this authority to someone else.

Clearly, this authority should not belong to anyone other than DBA managers.

To see the ADMIN OPTION privilege assigned to users:

When I run the code block, I see that the user “HR” has this privilege.

The command for authorization is the same.

However, users with SYSDBA authorization should be examined.

The sample output is as follows, I only see SYS.

If there are different users, it should be investigated.

However, users with the “DBA” role should be examined.

If you want to see the users with this authority;

When I run the above code block, I see that the user “HR” has this privilege.

If you see different users, you should research it.

If we want to get the authority;

Finally, let’s take a look at the CATALOG privileges. Catalog authorizations are examined in three different sections.

DELETE_CATALOG_ROLE; Audit trails are kept here in the database.

For example, this permission is needed to delete the AUD$ table.

If a user has this authority, he can delete the audit log records in the database.

You can request users with this authority with the following query.

To get authorization

SELECT_CATALOG_ROLE; It is the role where other users can list and query the tables in the SYS user.

To get authorization

EXECUTE_CATALOG_ROLE; It gives the user the right to run procedures and functions on the SYS user.


About Buğra PARLAYAN

Burgra Parlayan is an experienced Database and Weblogic Administrator. After completing his technical / relevant training he has got involved with a serious amount of projects. He successfully managed database upgrade, database migration, database performance tuning projects for various public institutions.Currently he has been employed by one of the leading financial institutions called Turkiye Hayat & Emeklilik as responsible administrator for Oracle Database and Oracle Middleware. He has been sharing his experience and knowledge by face to face training, personal blog and various social networking accounts to support the Oracle ecosystem continuously since 2010.

Leave a Reply

Your email address will not be published. Required fields are marked *