Database Reference
In-Depth Information
Time-based Privileges
I have previously used a method of time-based privileges that sets up a job for each privilege to be revoked. This
user management system is the intellectual property of a previous employer, so I can't release it here. However, Arup
Nanda has a method that uses an intermediate table to store the privileges to be revoked en-masse. Then there is a
regular job to remove them. I like Arup's method better, as it will be more efficient when there are many time-based
privileges to revoke. In a secure system we should be moving toward all human privileges being timed to some
respect. Arup's excellent time-based user management work is here:
I am working on a hardened version of Arup's code at this time and will release it in the future.
Time-limited access is also a way to reduce the risk associated with highly privileged accounts such as SYS.
We will look at break-glass systems in Part IV. First, how do we know who has what privileges?
UM_STATUS
UM_STATUS is a PL/SQL package that reports on the state of privileges for a given user or role within the database.
Oracle roles and privileges inherit recursively and can get quite complex to report on. I have written some
procedures to do this reporting in a clean and uncluttered manner. There is too much code at the following URLs to fit
into the topic, but here is the definition:
CREATE OR REPLACE PACKAGE UM.UM_STATUS AS
PROCEDURE role_sys_priv(user_in in varchar2);
PROCEDURE role_sys_tab_priv(user_in in varchar2);
PROCEDURE role_sys_pack_priv(user_in in varchar2);
PROCEDURE role_sys_view_priv(user_in in varchar2);
END UM_STATUS;
/
Following is a demonstration of how to use the code:
SQL> create user um identified by o;
User created.
SQL> grant create session, create procedure, select any dictionary, unlimited tablespace to um;
Grant succeeded.
SQL> create user umtest identified by o;
User created.
