CODEWITHSUNDEEP
oracle-databasedatabase-administration
Oscar
Oscar

Reputation: 31

Oracle 12c Grant Privileges Failing

I'm currently trying to grant a couple of simple privileges to an Oracle database user.

I have tried the following queries: 

grant all privileges to <username>

grant alter session to <username>

The second privilege is the one I actually need, but I decided simply to try and give the user all privileges to see if that would work. When I check the user's permissions using

select * from user_sys_privs;

everything seems to say NO.

I've even tried to grant the user dba privileges and that still fails. My end goal is to run scripts that require these permissions to be turned on.

Any help is greatly appreciated.

Upvotes: 1

Views: 718

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

everything seems to say NO

You're looking at the wrong thing. If the user_sys_privs view lists ALTER SESSION:

select * from user_sys_privs where privilege = 'ALTER SESSION';

USERNAME             PRIVILEGE     ADM COM
-------------------- ------------- --- ---
MY_USER              ALTER SESSION NO  NO

then the user does have that privilege.

The NO entries don't mean the privilege is not granted. The columns that is showing you are:

desc user_sys_privs

 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 USERNAME                                                                   VARCHAR2(128)
 PRIVILEGE                                                                  VARCHAR2(40)
 ADMIN_OPTION                                                               VARCHAR2(3)
 COMMON                                                                     VARCHAR2(3)

and they are described in the documentation:

ADMIN_OPTION - Indicates whether the grant was with the ADMIN option (YES) or not (NO)

COMMON - Indicates how the grant was made. Possible values:

  • YES if the privilege was granted commonly (CONTAINER=ALL was used)

  • NO if the privilege was granted locally (CONTAINER=ALL was not used)

As you didn't specify the admin option or any other modifiers, it's correct that both of those flags are set to NO.

What's probably confusing you is that all privileges are listed when you query for your user, because you did grant all privileges to <username>. You probably want to revoke all of those privileges, and only grant the specific ones the user actually needs. You'll then see a much shorter list when you query user_sys_privs - possibly only that single entry, depending on what else you need to retain for the user.

You might also want to consider using roles, though you sometimes need to have privileges granted directly anyway - if a stored procedure relies on them.

Upvotes: 1

Related Questions