Oracle public Permissions

One of the technical principles for ensuring Oracle database security is to carefully analyze the usage of the public user group. The public user group, as its name implies, represents every user in the database. Therefore, granting permissions to the public user group is actually granting corresponding permissions to every user in the database. This is a very useful shortcut for granting or revoking permissions. But it may also bring huge security risks, especially when trying to ensure that the database is run in a way with minimum permissions.

2.5.1 when to grant permissions to the public group

In many cases, granting public permissions is more practical and does not pose security risks. For example, most Oracle database application developers know that dual tables are very useful and do not contain sensitive information. This is also the case for many other processes and functions-the sysdate function is a good example, which is useful and does not cause security risks. Therefore, access to dual tables and sysdate functions by public groups does not pose security risks.

Unfortunately, it is difficult to know whether granting public permissions brings security risks. When developing an application, you need to carefully decide what permissions are granted to public-if you need to grant permissions.

At the same time, you also need to understand some problems that will not bring risks at present, but may bring risks in the future. For example, assume that a Web application contains a table to store users' selection items. Initially, users are allowed to save the background color, foreground color, and font type they used when creating their personal homepage in the table. This information is not sensitive and therefore can be viewed by any user.

Scott @ knox10g> Create Table user_prefs

2 (background_color varchar2 (6 ),

3 foreground_color varchar2 (6 ),

4 font_style varchar2 (20 ));

 

Table created.

 

Scott @ knox10g> grant select on user_prefs to public;

 

Grant succeeded.

 

 

Later, you may need to add a sensitive attribute to the table. For example, you can store hyperlinks of your favorite home pages and applications in the table.

Scott @ knox10g> alter table user_prefs add favorite_links varchar2 (250 );

 

Table altered.

After adding an attribute, the sensitivity of the table is completely changed. The permissions granted to the public group should also be revoked. The security rule for managing public group permissions is: do not grant public permissions as long as you are not sure whether the permissions are secure or not.

2.5.2 objects supported by Oracle

To effectively ensure the security of Oracle databases, you need to consider the permissions granted to the public by the developed or purchased applications and Oracle database objects.

You need to carefully consider the default public permission granted by two Oracle Objects:

● Access to data dictionary views some data dictionary views may leak user information and can be exploited to initiate attacks against databases.

● Execution of the process includes PL/SQL functions, programs, packages, and Java programs. These processes can execute many useful functions, such as opening a network connection, reading files from a running system, and setting authentication information for users or applications. All of these processes can be used later. in the database security process, for example, access control and audit.

1. Access to the dictionary view by the public group

By limiting access to sensitive data, Oracle database provides security protection measures for database dictionary metadata. Over time, the definition of "sensitive data" has developed several times: At first, sensitive data refers to table items such as encrypted user passwords; now, even the User Name of the database is considered as sensitive data. However, the public group can still access part of the data.

For example, the all_users view can be accessed by a public group and lists the usernames of each database mode. One common trick hackers use is to obtain and use valid user accounts to try to access other accounts. The database permission selection mode (mdys), default application accounts, and user accounts are all listed in the all_users view, which is becoming the most effective target for malicious users. Legal database users have become effective attack targets for databases! Moreover, malicious users can easily say, "Oh! Look! The <insert option name or your application here> option is installed in the database. Let me use the default password to access the authorized account !"

Therefore, you should consider revoking the permission of the public group to access certain database metadata. Using all as the prefix of the Sys object name is a good idea:

Select table_name

From dba_tab_privs

Where grantee = 'public'

And owner = 'sys'

And privilege = 'select'

And table_name like 'All % ';

2. damaged object

When revoking the access permission of a public group to default database objects, you should also know which existing programs or applications may be damaged while revoking the permission. The following example shows that 20 database objects become invalid after public access to the all_users view is revoked:

Sys @ knox10g> select count (*) from all_objects

2 Where status = 'invalid ';

 

Count (*)

----------

0

 

1 row selected.

 

Sys @ knox10g> revoke select on all_users from public;

 

Revoke succeeded.

 

Sys @ knox10g> select count (*) from all_objects

2 Where status = 'invalid ';

 

Count (*)

----------

20

 

1 row selected.

The damage is not irreparable. If an application depends on a permission granted to the public group, you can directly grant the corresponding permission to the application for repair. For the data dictionary view, the following lists the modes that require direct permission granting:

Sys @ knox10g> -- show whose objects are broken.

Sys @ knox10g> select distinct owner

2 from all_objects

3 where status = 'invalid ';

 

Owner

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

Dmsys

EXFSYS

Lbacsys

Sys

Sysman

XDB

 

6 rows selected.

In the preceding mode, some modes have the system permission select any dictionary, which allows you to access the all_users view. Some objects in these modes will be re-compiled without being granted any permissions, while some other modes need to be directly granted permissions on the all_users view. You can use the SQL functions in the simhei section in the following code to list the modes in which permissions still need to be directly granted. The running result of this code will list the modes that require direct permission granting:

Sys @ knox10g> -- create list of users who require

Sys @ knox10g> -- direct select privileges on all_users

Sys @ knox10g> select distinct 'Grant select on all_users'

2 | Owner

3 | ';' SQL _command

4 From (select distinct owner

5 from all_objects

6 where status =

7 'invalid'

8 and owner! = 'Sys'

9 minus

10 select grantee

11 from dba_sys_privs

12 where privilege =

13 'select any dictionary ');

 

SQL _command

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

Grant select on all_users to dmsys;

Grant select on all_users to EXFSYS;

Grant select on all_users to lbacsys;

Grant select on all_users to XDB;

 

4 rows selected.

By using the copy and paste method in the SQL _command value, you can directly grant the corresponding permissions to the user to be authorized. After authorization, invalid objects in these modes need to be re-compiled.

Unfortunately, it is almost impossible to predict the consequences of revoking permissions, that is why Oracle has not revoked the public permission on the database metadata view. In the Oracle database security guide, it is not easy to revoke the public DML permission:

Revoking public permissions may cause a series of impacts. If you revoke any permissions on DML operations from the public, all procedures in the database, including functions and packages, must be re-authenticated before being used. Therefore, you must be careful when granting or revoking DML-related permissions to the public.

3. Public permission in the program

The following analysis program grants the public execution permission. Note: More programs cannot be listed here, and these programs often change. The same problem occurs when the program security in the previously introduced view is ensured. However, it is very important to know which programs can complete what functions are necessary to understand their security risks-if there are security risks.

The programs starting with DBMS % and utl % are the most noteworthy:

Select table_name

From dba_tab_privs

Where grantee = 'public'

And owner = 'sys'

And privilege = 'execute'

And table_name like 'dbms %'

Or table_name like 'utl %'

Order by 1;

Warning:

Do not mean your evaluation of these programs or sys objects. All objects and applications in the database should be evaluated.

In the Oracle database security guide, we recommend that you revoke the public execution permissions on utl_smtp, utl_tcp, utl_http, and utl_file. We should not only revoke these execution permissions, but also remember that these execution permissions restrict access to applications, users, and objects.

As in the metadata example above, applications often rely on granting public permissions to these programs. In order to successfully Delete these permissions, you not only need to understand their dependencies, but also need to solve any problems arising during permission revocation. Chapter 6th provides an example of revoking the execution permission of the dbms_session package.