0

I want to understand the privileges better, but I'm not able to find some useful info using Google.

I thought that I needed ALTER USER privilege to be able to unlock user and change password. But currently I'm on a machine, where I can change the password (also for other users), but I'm not able to unlock locked user. Why is that?

I tried also

SELECT * FROM USER_SYS_PRIVS ;

ant the result is:

TEST_USER   CREATE OPERATOR NO  NO
TEST_USER   ALTER SESSION   NO  NO
TEST_USER   CREATE SESSION  NO  NO
TEST_USER   CREATE DATABASE LINK    NO  NO
TEST_USER   CREATE SYNONYM  NO  NO
TEST_USER   CREATE INDEXTYPE    NO  NO
TEST_USER   CREATE MATERIALIZED VIEW    NO  NO
TEST_USER   CREATE TRIGGER  NO  NO
TEST_USER   GLOBAL QUERY REWRITE    NO  NO
TEST_USER   CREATE SEQUENCE NO  NO
TEST_USER   CREATE CLUSTER  NO  NO
TEST_USER   CREATE TYPE NO  NO
TEST_USER   CREATE PROCEDURE    NO  NO
TEST_USER   CREATE VIEW NO  NO
TEST_USER   CREATE TABLE    NO  NO

Error, when tried unlock:

alter user other_user account unlock
Error report -
SQL Error: ORA-01031: insufficient privileges
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to perform a database operation without
           the necessary privileges.
*Action:   Ask your database administrator or designated security
           administrator to grant you the necessary privileges

closed as off-topic by Paul White, dezso, RLF, Vérace, Colin 't Hart Jun 29 '15 at 14:03

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Paul White, dezso, RLF, Vérace, Colin 't Hart
If this question can be reworded to fit the rules in the help center, please edit the question.

  • You wrote you had ALTER USER, still it's not in the output. Provide the full output with a successful password change and unlock attempt, and include SELECT * FROM SESSION_PRIVS instead if USER_SYS_PRIVS with the user you are trying to unlock others. – Balazs Papp Jun 26 '15 at 18:08
  • My testing was wrong, I'm sorry - closing... – Betlista Jun 29 '15 at 11:21
  • Kindly delete the question - makes like easier for everybody! – Vérace Jun 29 '15 at 13:32
  • Believe me, I wanted, but: "Question with answers cannot be deleted" :-/ – Betlista Jun 29 '15 at 14:15
1

you have not shown the roles TEST_USER is granted. Likely they have been granted the DBA role which allows ALTER USER.

see the documentation for details. This query will show the roles a user has

SELECT * FROM DBA_ROLE_PRIVS where grantee = 'TEST_USER'

of course this requires privileges too, unless you have the CATALOG role, if you select from this table you can alter a user

  • Thanks for a reply, USER_ROLE_PRIVS for the user is empty and I have no privileges for DBA_ROLE_PRIVS view, but should be the same, right? So no DBA role is granted. If so, I'd be able to unlock the user/account, right? – Betlista Jun 25 '15 at 18:18
  • Thanks for documentation, I tried session_privs and session_roles from documentation and nothing changed - 15 privs + 0 roles. – Betlista Jun 25 '15 at 18:23
1

ALTER USER should be enough, see below.

SQL> select banner from v$version where rownum = 1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

SQL> create user u1 identified by u1;

User created.

SQL> create user u2 identified by u2 account lock;

User created.

SQL> grant create session to u1, u2;

Grant succeeded.

SQL> grant alter user to u1;

Grant succeeded.

SQL> conn u1/u1
Connected.
SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
ALTER USER
CREATE SESSION

SQL> select * from session_roles;

no rows selected

SQL> alter user u2 account unlock;

User altered.

SQL> conn u2/u2
Connected.
SQL>
  • Maybe it was not clear from the question, so I modified a bit - I have this privilege, but still cannot unlock the account. – Betlista Jun 25 '15 at 19:05

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