0

I'm looking to do some database level system triggers as I'm building a proxy access system into my database to stop my team sharing passwords. I have also granted roles, but some development needs require full schema access.

I know I can manage certain actions such as ALTER USER etc through the use of a system level trigger if certain entries (e.g. USER) appear in the ora_dict_obj_type attribute at the time of the trigger event. However, I'm really struggling to find a straightforward list of all the potential attributes that could be valid in ora_dict_obj_type so am unable to do my coding.

I've checked dba_objects and all_objects and they are missing content I know can get returned as attributes in ora_dict_obj_type so can I please be directed to a full list of the potential attributes?

Thanks

Sean

2 Answers 2

0

I have a DDL logging trigger that captures ora_dict_obj_type. On my busiest databases, these are the distinct values it's captured over the past year:

COLUMN
CONTEXT
DATABASE LINK
DIRECTORY
FUNCTION
INDEX
INDEXTYPE
JAVA
LIBRARY
OBJECT PRIVILEGE
OPERATOR
PACKAGE
PACKAGE BODY
PROCEDURE
PROFILE
ROLE
SEQUENCE
SNAPSHOT
SYNONYM
TABLE
TABLESPACE
TRIGGER
TYPE
TYPE BODY
USER
VIEW

So pretty close to the types you'd see in dba_objects, but with a number of additional ones. I'm sure there are some others we haven't seen, but this is probably the bulk of what you'd see. Does this help?

0

I'm building a proxy access system into my database to stop my team sharing passwords.

Why reinvent the wheel? Use Oracle's built-in proxy authentication to prevent password sharing. From a blog post I wrote a couple of years ago, here:

The problem is that application developers have (gasp!) been known to share the username and password for the application schema owner account and use it to connect to the database from their various development tools...

One way to combat this issue of access proliferation is to use a method of user authentication that Oracle calls “proxy” authentication. It allows a user to connect to a database as one user and on connection become a different user. This capability was originally deployed by Oracle as a way for applications to authenticate end users to individual database accounts through a common application account, but it works just as effectively the other way around.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> create user app_schema no authentication proxy only connect;

User created.

SQL> grant connect, resource to app_schema;

Grant succeeded.

The “no authentication” option creates the shared account without credentials. This eliminates the need to maintain a password or certificate for an account that will never be used directly. The “proxy only connect” option allows only proxy connections to the shared account. The “connect” and “resource” roles grant basic privileges to use the account and create objects. Next, I will create a sample development user:

SQL> create user dev_user identified by oracle;

User created.

Now the application schema can be altered to allow connections from individual developer accounts, like this:

alter user app_owner grant connect through dev_user;

Note the syntax: the APP_OWNER schema is altered to allow connection by or through the DEV_USER account. This is not a privilege that is assigned directly to the development user. Once this proxy privilege has been assigned, the developer can connect to the application schema using their personal credentials. All they need do is append the application account name in brackets to the end of their development account name, like this:

SQL> connect dev_user[app_schema]/oracle
Connected.
SQL> show user;
USER is "APP_SCHEMA"

By connecting in this way, the developer can still perform needed actions but need never be aware of the application owner account’s real password (assuming one was even assigned). The proxy account name (the developer’s personal account) is available in the system session context, and can be automatically made visible in the v$session view through a database trigger so that the DBA can tell who is connected to shared accounts at all times.

See the blog post for details of the trigger.

2
  • Hi pmdba, I set up the proxy accounts using Oracle's functionality, based on previous guidance from you. What concerned me at the time was that granting proxy account access would enable developers to become the application account and have full permissions, including the capability to change password. I therefore wrote a system trigger, based on detecting the ALTER USER command through ora_dict_obj_type, to block such changes to the application account. I now wish to extend these limitations, hence my question about what other entries are valid for ora_dict_obj_type
    – SeanGaff
    Commented Jul 5, 2023 at 8:26
  • What kind of activity - other than changing the password - are you looking to restrict? Generally proxy access is intended to let the user have the same privileges as the shared account, or to use a specific proxy role with a subset of those privileges.
    – pmdba
    Commented Jul 6, 2023 at 1:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.