"#smartdb & best practices dictate granting fine-grained object level privs.
Which user(s) should do the granting?
1) Objects owner(s)
2) SYS
3) adedicated granting schema
4) personal account of person doing the grant
5) other (please elaborate)"
This is an interesting question and is one that comes up in my training classes when i teach them live in person or on line via webex. Details of all my up-coming online classes and also a live in person class in York, UK are on our Public Training dates page. I will be adding at least one more public live in person training date in York soon and possibly two dates and possibly one in Europe as well as I am also working to offer live in person classes in some choice other countries with partners (more soon, when if/its agreed!).
Back to the question; sorry for the interlude so soon in the post. People regularly do ask me this same question; how do we make grants on objects in the database such as tables, views or procedures; do we do it as the schema; do we do it as the DBA? or what?
A similar issue occurs related to this question in that who do we create the objects "as" in the database in the first place? do we log onto the schema and with the schemas CREATE PROCEDURE and CREATE TABLE system privileges run the scripts as "create table...", "create procedure..." or do we connect as a DBA and use his CREATE ANY PROCEDURE or his CREATE ANY TABLE and alter all the scripts to say "create scott.table...." or "create scott.procedure....". There are issues with both of these so lets address these first as we cannot make grants until we have created objects.
So, in 18c Oracle has created a supported way to have a schema that cannot be logged into. This is great BUT i have been doing this for years anyway. The new syntax is CREATE USER blah NO AUTHENTICATION. We can do the same in versions before 18c by using the syntax CREATE USER blah IDENTIFIED BY VALUES... so the only difference really is that its supported and the AUTHENTICATION_TYPE column of SYS.USER$ is now set to NONE rather than PASSWORD even if the password hash value was all zeros or in DES syntax some non HEX text.
So Oracle clearly also think its a good idea to not log into the schema at all. I agree and have been telling people for years to not do that. Also in a lot of systems that I perform audits I find that not only do support and DBAs log into the schemas so does the applications from application servers or pc based logins.
So my view is to not use a DBA account either that therefore needs %ANY% type grants. Also a developer or release account with these types of rights is not correct either as it means that these accounts can also change any other schema except SYS with these rights. Also if you take this approach the code needs to be written as CREATE SCOTT.OBJECT not CREATE OBJECT; not neat and not flexible if you decided to do application/schema as a service.
A better approach is to write code as CREATE TABLE.... CREATE PROCEDURE.... and use proxy. Create an account that can be used by the release team to proxy to the schema. Then when it logs in with user[connect to user]/password you are in all intents and purposes the schema BUT you can audit actually who did the changes by either using proxy audit .... ON BEHALF OF... or you can simply use a log on trigger and get the proxy ID and link it to normal audit records. In this way you can audit every action of the release without the need to modify the application audit to capture everything just for the release. You can then instead use the AUDIT ALL STATEMENTS IN SESSION CURRENT from a log on trigger; capture the proxy as well when the connection is proxying to the schema. In this way the proxy user cannot disable this audit; another security feature.
There are a couple of caveats - see my 18c blog post from last week. You cannot LOCK the account you want to proxy through - that stops proxy from working. Also even if you have locked the schema with an impossible password or the new NO AUTHENTICATION in 18c then you cannot proxy if you don't grant CREATE SESSION to the schema - see my 18c post for an example.
A better addition to this is to grant the CREATE rights to a role and then grant the role to the schema. Then the role should be granted only for creation time and maintenance windows. All other times it should be revoked. This means that no CREATE SESSION, no direct connection either with 18c or with BY VALUES and no connections via proxy (CREATE SESSION is revoked). This gives layers of security. The maintenance needs the DBA to grant the role BUT the DBA doesn't need CREATE ANY type rights as the PROXY user uses those via the role and the proxy connection.
So back to Patricks question; you can guess the answer; use the same PROXY connection to connect to the schema, be audited for all actions, so we know who actually did it and make the grants as the SCHEMA but through the PROXY. So to answer Patricks points:
1) Don't connect direct to the schema in this way we can control schema access
2) Not SYSDBA; we should not use SYSDBA for application support. its the superuser (sort of) and we should not use it on a daily basis
3) Don't use a dedicated granting schema as this means we would have to grant the privileges WITH GRANT OPTION - this is bad as it moves the chain of control from the schema owner to another user who has the control to grant to whoever he pleases. This moves the security out a layer. Bad
4) A personal account of the person doing the grant - not sure what he means by this? - either way it would have to be an account with an ANY right or a GRANT OPTION right; again bad
5) others - a DBA, not SYSDBA, same as above we don't need the %ANY% rights
The solution is PROXY as it's neat; to allows us to revoke the CREATE rights during run time by attaching those to a ROLE and making the schema less of a risk if its attacked via SQL Injection or similar and also allows us to revoke CREATE SESSION via the same ROLE so making PROXY not possible with out two steps; grant the role back; connect as a proxy; enable audit in session current.
Hope this helps! - Sorry i think i stole that phrase from Tim..
