Table of contents
Preamble
The non existing privilege to grant someone else the right to create objects in your schema is a recurring debate where I work and most probably in many other places. The recurrent question is “can you grant me the right to create a table in another schema ?” and as you know the only possible solution is to grant the high privilege CREATE ANY TABLE.
With this CREATE ANY TABLE high privilege comes a bunch of other “required” privileges like DROP ANY TABLE, ALTER ANY TABLE and so on. By laziness, and to be fair, also because sometimes it is not that easy to achieve comes the SELECT ANY TABLE, UPDATE ANY TABLE, INSERT ANY TABLE and DELETE ANY TABLE privileges. For this last bunch of privileges you can still argue that the owner of the object(s) can grant what’s required and close the thread. But for the CREATE ANY TABLE there is no trivial solution…
One excellent reference when designing your application with an Oracle database as a backend is the website of Pete Finnigan and in particular this presentation:
Design For Your Database Applications – Least privilege, data and ownership
I tend to mostly agree on all (hum) but for some parts I would simplify and act differently, let me clarify…
The not so bad solution to avoid CREATE ANY TABLE
The least privilege approach is obviously a must and the recent privilege analysis feature that has been made free can be of great help.
The application must also NOT connect with the owner of the database objects and you must have an applicative dedicated account with only minimum required privileges (read only or whatever). The password of this account must not be shared with end users and ideally you change it regularly with an automatic solution (CyberArk Application Access Manager (AAM)). The password of this account must not be found in configuration file of your applicative servers and you should use a password vault to sore it (Azure Key Vault for example).
Inside the database you must also split the different parts of the application or the many applications is their own dedicated database account owner. If all your applications reside is same database account owner then it is impossible to segregate data access between batches and scripts that are using the only database account owner to connect to database (not so many hopefully).
I also like to set this database account owner an Operating System authenticated account (in live, not necessarily on QA/Dev/T&I) to avoid remote connection through SQL*Net to it. In other word the account authentication is backported to the operating system and not to the database. Said differently you need to have an OS account with exact same name (os_authent_prefix) as the database and authenticate to it before being able to connect to the database. Not being able to use, for example, SQL Developer or Toad to access those accounts might be seen as a limitation but this is more for me an increased security and it avoid mistakes patching live while thinking being on a development platform (true story !):
Example of an OS authenticated account:
SQL> create user app_owner identified externally; User created. |
Remark:
For increased security there is also an option to add a certificate to those OS authenticated account. You also have the option to create account identified by your enterprise LDAP directory (IDENTIFIED GLOBALLY) but this is a bit more complex to manage and requires more infrastructure.
Then remains the usual case where you need to patch your production data model and so where you need to connect with the schema owner. This access might be required by internal people or by a third party to whom you bought the software.
I rate not a big deal to share this password for short period under control and to immediately change it once the job has been done ! If the schema owner is an OS authenticated account you might already have a strong access control (CyberArk, for example, with/without sudo in parallel) to the operating system of your servers so again not a big deal.
Another solution is the usage of Oracle proxy users but I rate this an extra complexity and in any case at the end you have an additional account able to connect “as if” your database schema owner so for me a new security hole to handle…
Oracle proxy user
In case you decide to go for Oracle proxy users a small test case to demonstrate it. I create my database schema owner with. The SELECT_CATALOG_ROLE role is just an example for the proxy user:
SQL> create user app_owner identified by "password"; User created. SQL> grant connect, resource, select_catalog_role to app_owner; Grant succeeded. SQL> alter user app_owner quota unlimited on users; User altered. |
Then the third party/developer account. Note this account has no privilege, even not the one to connect (CREATE SESSION). This account will be able to proxy my database schema owner but only with CONNECT and RESOURCE role, not with SELECT_CATALOG_ROLE:
SQL> create user supplier_owner identified by "password"; User created. SQL> alter user app_owner grant connect through supplier_owner with role connect, resource; User altered. SQL> set lines 200 SQL> col proxy for a20 SQL> col client for a20 SQL> select * from proxy_users; PROXY CLIENT AUT FLAGS -------------------- -------------------- --- ----------------------------------- SUPPLIER_OWNER APP_OWNER NO PROXY MAY ACTIVATE ROLE |
I create a test table and confirm I can select dictionary views:
SQL> connect app_owner/password@pdb1
SQL> create table test01(val number, descr varchar2(20)) tablespace users;
Table created.
SQL> insert into test01 values(1, 'One');
1 row created.
SQL> commit;
Commit complete.
SQL> select count(*) from v$session;
COUNT(*)
----------
95
SQL> select table_name from user_tables;
TABLE_NAME
--------------------------------------------------------------------------------
TEST01 |
Now I connect with supplier_owner and proxy app_owner account. I confirm I have no access to dictionary view and I create a new table:
SQL> connect supplier_owner[app_owner]/password@pdb1
Connected.
SQL> show user
USER is "APP_OWNER"
SQL> select * from test01;
VAL DESCR
---------- --------------------
1 One
SQL> select count(*) from v$session;
select count(*) from v$session
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> create table test02(val number, descr varchar2(20));
Table created. |
As expected the second test table is owned by app_owner:
SQL> connect app_owner/password@pdb1 SQL> select table_name from user_tables; TABLE_NAME -------------------------------------------------------------------------------- TEST01 TEST02 |
Conclusion
Overall for a production database, and moreover for one containing sensitive figures the ANY privileges should not be used for any account. The only exception we have done on this is for an internal application that people use to patch figures on live database where they have no direct access. Yes time to time business people need to correct mistakes on production figures, but this patching application has an Enterprise Directory authentication and we log all what is done.
But for this patching application we implemented Secure external password store (SEPS) and the password of those powerful accounts is ultra secure (more than 15 characters, uppercase, lowercase, number, ponctuations symbols) and not known by anyone (the DBA that set it has immediately forgotten it).
References
