Selective Privilege Enablement

One of the greatest advantages to using roles is that roles provide the ability to selectively enable and disable privileges for the user. Unlike directly granted system and object privileges, which are enabled (and thus “on”) all the time, roles can be granted to a user but not enabled. The privileges for the user are not “on” until the role is enabled.

I’ve seen people try to mimic selective privileges by having their application, at execution time, dynamically grant and revoke object privileges. For example, they might create a procedure that the application will call to enable the privileges. Likewise, there will be a procedure to undo the user’s privileges.

In the following example, privileges to control access to DATA_OWNER’s objects have been granted to the SEC_MGR schema.

system@KNOX10g> -- delegate privs to the security administrator system@KNOX10g> GRANT ALL ON data_owner.t TO sec_mgr   2    WITH GRANT OPTION; Grant succeeded. system@KNOX10g> GRANT EXECUTE ON data_owner.foo TO sec_mgr   2    WITH GRANT OPTION; Grant succeeded.

If you were to implement dynamic privilege enablement, you might create a program similar to the following:

sec_mgr@KNOX10g> CREATE OR REPLACE PROCEDURE set_privs   2  AS   3  BEGIN   4    EXECUTE IMMEDIATE    'grant select on DATA_OWNER.T to '   5                      || USER;   6  END;   7  / Procedure created. sec_mgr@KNOX10g> CREATE OR REPLACE PROCEDURE unset_privs   2  AS   3  BEGIN   4    EXECUTE IMMEDIATE    'revoke select on DATA_OWNER.T from '   5                      || USER;   6  END;   7  / Procedure created. sec_mgr@KNOX10g> GRANT EXECUTE ON set_privs TO scott; Grant succeeded. sec_mgr@KNOX10g> GRANT EXECUTE ON unset_privs TO scott; Grant succeeded. 

For the user to selectively enable their privileges, the application simply calls the SET_PRIVS procedure while logged in as the appropriate user.

scott@KNOX10g> select * from data_owner.t; select * from data_owner.t                          * ERROR at line 1: ORA-00942: table or view does not exist scott@KNOX10g> exec sec_mgr.set_privs; PL/SQL procedure successfully completed. scott@KNOX10g> select * from data_owner.t; D - X

This is a bad design. The major flaw exists because the privileges aren’t restricted to the user for just that application. A user may log in to the database and gain access to the object without having to do anything. This is because they query after the SET_PRIVS has been executed and before the UNSET_PRIVS. Do not use this design.

An alternate design for supporting selective privileges is based on assigning the roles to a user but not enabling the role by default. For example, to prevent a user from accessing a particular application’s data, you might force the users to possess the APP_USER role. An application user is granted the role, but the role is not enabled by default. When the user accesses the database via the application, the application knows it has to enable the role and does so transparently for the user:

 sec_mgr@KNOX10g> CREATE ROLE app_user_role; Role created. sec_mgr@KNOX10g> -- Grant privileges to role sec_mgr@KNOX10g> GRANT ALL ON data_owner.t TO app_user_role; Grant succeeded. sec_mgr@KNOX10g> -- Grant role to user(s) sec_mgr@KNOX10g> GRANT app_user_role TO scott; Grant succeeded. sec_mgr@KNOX10g> -- Disable this role by default. sec_mgr@KNOX10g> -- Privileges are not available sec_mgr@KNOX10g> -- until role is enabled sec_mgr@KNOX10g> ALTER USER scott DEFAULT ROLE ALL   2    EXCEPT app_user_role; User altered.

If the user logs in via SQL*Plus and tries to query the application’s tables, they will fail because the privileges to do so aren’t available until the role is enabled:

scott@KNOX10g> SELECT * FROM data_owner.t; SELECT * FROM data_owner.t                          * ERROR at line 1: ORA-00942: table or view does not exist scott@KNOX10g> SET ROLE app_user_role; Role set. scott@KNOX10g> SELECT * FROM data_owner.t; D - X

As you may conclude, this solution doesn’t appear to be much better than the procedural-based method. The only difference is the set role implementation only enables the privileges for the database session, whereas the SET_PRIVS procedure will enable the privileges for all database sessions.

In the preceding examples, knowing or not knowing the existence of a procedure or role that has to be executed or enabled provides no security. Basing security on the simple knowledge of things that can be easily guessed or derived is called “security through obscurity,” and it’s not considered a best practice when used alone.

Caution 

Forcing applications and users to explicitly enable roles or privileges does not provide adequate security, and believing it does only fosters a false sense of security.

Selective Privilege Use Cases

The real power of selective privileges implemented via roles is exploited when using roles that require something other than just knowing the role’s name. You will look at two ways to secure roles soon. Before you do that, you’ll look at several important use cases that frame the complexities and requirements for selective privileges through roles.

Privileges Only When Accessed via an Application

One frequent requirement is to only allow user database access when the access is via an application. This is very popular with web applications. You might wonder why this is so hard. The answer: standards and interoperability.

Normally, standards and interoperability are good things. In the security world, standards and interoperability for establishing database connections can be a challenge, because they may facilitate unwanted access into the database. The Oracle database supports numerous applications and protocols—ODBC, JDBC, Oracle Net clients, Web Services, HTTP, FTP, and so on. These protocols and capabilities are important to ensuring interoperability with commercial applications as well as facilitating emerging ways to access data.

From a security standpoint, each one represents an additional window into the database that needs to be secured. The best mechanism for securing them is to shut them off. That may not be a practical choice if other applications need access to the protocols.

What you typically see are users accessing the database via a known application and protocol. The security requirement then is to ensure that this is the only way the users can get to the database. The applications may very well be providing extra layers of security to prevent the users from poking around in the database. Ensuring the users aren’t in the database running ad hoc queries is a good thing. It doesn’t take much for a user to intentionally or inadvertently launch a denial of service attack via some poorly formed SQL.

The net, as seen in Figure 7-2, is that you want to restrict access to your application tables to the application and the users such that the users are only accessing the tables via the application.

image from book
Figure 7-2: Frequent privilege requirement constrains user access to the database only when accessed via an application.

Privileges Based on User and Application

Refining the problem a bit, there is a more complex problem in which the security privileges are based not only on the user, but also on the application. As seen in Figure 7-3, the user may have several applications accessing the same database. The difference between this and the one just described is you’re assuming the user has access to the same database through multiple applications. This is a popular model for two application types. The first is a web-based application. The second is an ad hoc reporting tool.

image from book
Figure 7-3: Privileges for a user accessing the database via multiple applications should vary depending on which application the user is using.

The figure depicts the security concern: the user will point the ad hoc query tool at the web application data or the financial application data. Because the application data may not rely on database security alone (if at all), the user may have full access, or more access, than you’d like. This application data wasn’t intended to be accessed in an ad hoc manner. To maintain least privilege, privileges should be based on the user and the application, not the user and all the applications.

Privileges Based on User, Application, Location, and Time

Another variation on privileges can be seen when the same user, using the same application, accesses a database in different ways, from different places, at different times. The point is that arbitrarily complex security policies may be required.

As seen in Figure 7-4, the user may be accessing the application from within her office. Access to the office is physically controlled by the security guard at the entrance of the building and a lock on the office door. Because of the physical security controls, a user accessing the application from within their office provides some sense of assurance that it’s really the user. Therefore, the user is allowed all privileges necessary. When the user travels to a field office, the location may still be controlled, but there is less assurance that it’s really the user. Therefore, they only get read and insert privileges. Finally, access via a wireless device is trusted the least. Wireless devices are lost and stolen frequently. As such, the user has the ability only to read data. No data manipulation would be allowed. All of these could be constrained to certain hours of the day. If the user tries to gain access on Sunday morning at 3 A.M., they are not allowed.

image from book
Figure 7-4: User privileges may vary based on access method, location, and time of day.

The common thread among all these use cases is that the privileges aren’t based on the user’s identity alone. Privileges should be based on everything you know—the application, how the user authenticated, where they are, the time of day, and of course, the user’s identity. The trick to making this work is selective privilege support. Roles and their ability to be enabled and disabled are critical to making this work. Just as important is the ability to secure the selective enablement of these roles and privileges.

There’s also a philosophical reason why securing the roles is important. If the roles are simply enabled whenever the application makes the SET ROLE call, then the application is in fact controlling the database privileges. That is, the database has no say in the matter. It’s just sheepishly following along with whatever the application tells it. This is not defense in depth and therefore is not optimal. Let’s see how to secure database roles. It involves two variations on roles: password-protected roles and secure application roles.



Effective Oracle Database 10g Security by Design
Effective Oracle Database 10g Security by Design
ISBN: 0072231300
EAN: 2147483647
Year: 2003
Pages: 111

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net