Skip to Main Content
  • Questions
  • More Fine-Grained 'ALTER USER' Privilege

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Thomas.

Asked: June 13, 2017 - 7:34 am UTC

Last updated: June 19, 2017 - 3:15 am UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

I am currently looking for a more fine-grained approach to the user management within an Oracle 11g Release 2 (soon to be 12.1.0.2) Database:

There is an idea to give some users the permission to manage some aspects of a user account such as:

- Change the password of a user
- Expire a password
- Lock or unlock an account

However, it is explicitly not wanted that the pre-selected profile is changed as it also contains a password validation procedure .
When granting 'ALTER USER' it is possible to make that change :

SYS@TEST > create user dummy identified by dummy account unlock;

User created.

SYS@TEST > grant connect, resource, alter user to dummy;

Grant succeeded.

SYS@TEST > connect dummy/dummy
Connected.

DUMMY@TEST > alter user scott profile default;

User altered.


That is excatly what is not intended to happen (but of course working as designed) .
The only idea so far is to create a function that is owned by a privileged account that allows just the
operations that are to be permitted and then grant execution rights to the users allowed to make the changes.
This would have the charm to allow to perform a filtering of the ID's the changes are to be permitted for
but still something out of the box would be better for maintenance and Migration reasons.

The same happened in the Enterprise Manager Cloud Control (all or nothing).
Is there any other way to achieve something like this?

and Connor said...

In 12.2, we have a thing called lockdown profiles which lets more granular access to privileges

http://docs.oracle.com/database/122/DBSEG/configuring-privilege-and-role-authorization.htm#DBSEG-GUID-AB5E62DB-7E2A-4B5A-BA96-A2BD2DF15275

But until then, you need to manage the control yourself. For example, you can create a routine as a privileged user to allow certain facilities, eg

create procedure sys.alter_profile(p_user varchar2, p_profile varchar2) is
begin
  execute immediate 'alter user ...'
end;


grant execute on sys.alter_profile to [selected users]

But since you are considering upgrading, why not go to 12.2 and save yourself the trouble :-)

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library