Re: create user with global rights ..?

"Oliver Otto" <ootto_at_genese.de> wrote in message news:3D4C03D7.6F84566F_at_genese.de...
> Hallo,
>
> how can I create a user with global rights ..? Last time I setup all
> grants for every table by hand but this time I do want to make it easier
> within my test enviroment. Is there really not way to give a user all
> rights ..?
>

If the issue is purely one of wanting to work with tables, then the SELECT ANY TABLE, INSERT ANY TABLE,UPDATE ANY TABLE and DELETE ANY TABLE privileges can be granted.

(Incidentally, a complete list of privileges is available by doing a select * from system_privilege_map).

Or you could simply 'grant dba to user', since the DBA role includes these same privileges (and most other privileges besides, so that comes very close to 'giving a user all rights').

This is, of course, not best practice, and renders your database wide open to eventual abuse. But it answers your question.

> I try to configure a user based on sys or sysdba but this do not work. I
> got the error message:"you do not have enough rights". What a funny
> error message.
>

Why? It's only funny if you don't understand what the sysdba privilege means. It's a privilege which implies the ability to startup, shutdown, backup, recover and create databases. It's therefore obviously a jealously-guarded privilege, since granting it to someone is tantamount to inviting that person to wreak complete havoc on your database. Therefore, you have to already possess the SYSDBA privilege to grant it to someone else. And because use of this privilege can be authorised *either* by being written into the password file, *or* by membership of an appropriate operating system group, it can only be granted within the database to a database user when you are using an EXCLUSIVE password file.

> Now you ask yourself why don't he use sys or system user. The anser is
> near, I do want to run a Script from this new user which create some
> Views based on tables and views from sys. I am not able to give these
> rights globally :-( Any hints ..?!?
>

Personally, this sounds like a production database. Therefore, you should grant the privileges on each table, individually, as you did before. Granting privileges with the word 'any' in them is effectively unsecuring your database. Granting the DBA role would likewise be a quick fix, but utterly unsuitable for a production environment.

Regards
HJR
> I hope I describe the problem ok...
>
> Thanx a lot
>
> Oliver
>
Received on Sat Aug 03 2002 - 15:06:45 CDT