grant all privileges to an user in Oracle 12c

1
votes

In Ora11g I used to grant all privileges to a user as follows.

create user xx identified by psw;
grant create session, grant any privilege to xx;
grant all privileges to xx;

But in Ora 12c when I execute grant privileges, I'm getting the below ERROR.

Error starting at line : 2 in command - grant create session, grant any privilege to xx Error report - ORA-00604: error occurred at recursive SQL level 1 ORA-20997: "GRANT ANY PRIVILEGE" grants not allowed ORA-06512: at "RDSADMIN.RDSADMIN", line 79 ORA-06512: at line 2 00604. 00000 - "error occurred at recursive SQL level %s" *Cause: An error occurred while processing a recursive SQL statement (a statement applying to internal dictionary tables). *Action: If the situation described in the next error on the stack can be corrected, do so; otherwise contact Oracle Support.

I have tried the answer suggested in 1 and it does not seems work.

1. Regarding Users in Oracle 12c

It is getting the below ERROR.

GRANT All PRIVILEGE TO name Error report - ORA-00922: missing or invalid option 00922. 00000 - "missing or invalid option" *Cause:
*Action

Your help on "grant all privileges to a user in ora12c this is much appreciated.

Thanks!

1
oracleoracle12cprivileges
Is this a single instance, or a container database? If it is a container database, you will have to connect to the appropriate pluggable database before you will be able to issue grants, unless you are working with common users (usernames are prefixed with c## and are handled a little differently.) I'm guessing that you are connecting to the root container and trying to create the user there, rather than in the pluggable database in which it belongs. To change to the pluggable, execute the following "alter session set container=[NAME OF PLUGGABLE DATABASE]" – 1991DBA
Also check for "BEFORE GRANT ON DATABASE" triggers - that error stack often shows up when somebody set up a trigger to specifically block grants. – kfinity
this sounds like you are using oracle on amazon rds and your account does not have the required privileges. – steve

1 Answers

2
votes

Yes. I was able to grant privileges with a namespace.

Create user xx identified by psw account unlock; 
grant connect to xx;
grant create session, create table, create sequence, create trigger to
xx; 
alter user xx quota 5M on USERS;
commit;

if you need to give unlimited quota, you use this.

ALTER USER xx quota unlimited on USERS;
commit;