Home » RDBMS Server » Security » Give users the bare minimum privs
Give users the bare minimum privs [message #220669] Wed, 21 February 2007 09:20 Go to next message
bogey
Messages: 53
Registered: February 2006
Member
My users where assigned CONNECT and RESOURCE roles. I'd like to give them insert, update, and delete priv's on their own tables.

When a user is created do they have these priv's on their objects already? I'm assuming I must also give them CREATE SESSION also?

thanks.
Re: Give users the bare minimum privs [message #220670 is a reply to message #220669] Wed, 21 February 2007 09:22 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
RESOURCE could be dangerous.
Grant a CONNECT and a explicit quota on the default tablspace assigned.
In 10g, things are little different.
Re: Give users the bare minimum privs [message #220672 is a reply to message #220669] Wed, 21 February 2007 09:27 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,

When a user is created do they have these priv's on their objects already?

Yes.

SQL> conn sys@orcl as sysdba
Enter password:
Connected.
SQL> create user boo identified by boo;

User created.

SQL> alter user boo default tablespace USERS quota 2m on users;

User altered.

SQL> GRANT CREATE SESSION TO boo;

Grant succeeded.

SQL> grant resource to boo;

Grant succeeded.

SQL> conn boo@orcl
Enter password:
Connected.
SQL> create table boo ( name varchar2(20));

Table created.

SQL> insert into boo values ('booo');

1 row created.

SQL> update boo set name = 'BOO';

1 row updated.

SQL> delete from boo;

1 row deleted.


If you grant RESOURCE role that is you grant below privs.
SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION >>> explicit grant.
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE

10 rows selected.
Re: Give users the bare minimum privs [message #220675 is a reply to message #220672] Wed, 21 February 2007 09:32 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> CREATE SESSION >>> explicit grant.
Actually, that is implicit.
Re: Give users the bare minimum privs [message #220680 is a reply to message #220675] Wed, 21 February 2007 09:44 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
Actually, that is implicit

Actually i don't understand your reply.
on my previous example i grant RESOURCE role or CREATE SESSION two privs to user.

that is why i indicate EXPLICIT for create session privs.

regards
Taj

[Updated on: Wed, 21 February 2007 09:56]

Report message to a moderator

Re: Give users the bare minimum privs [message #220682 is a reply to message #220680] Wed, 21 February 2007 09:54 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Please ignore. I misread.
Re: Give users the bare minimum privs [message #220722 is a reply to message #220669] Wed, 21 February 2007 13:20 Go to previous messageGo to next message
bogey
Messages: 53
Registered: February 2006
Member
I saw one example where you created a table. This I don't want them to do. I already have users creating views and tables and then running extremely long queries that are tying up system resources.

What I should of asked in the beginning is "What do you normally let your users do OR what privs do you give them?"
Re: Give users the bare minimum privs [message #220752 is a reply to message #220722] Wed, 21 February 2007 22:10 Go to previous message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
What do you normally let your users do OR what privs do you give

It is depand on user requirement. according there work and need we grant privs.

but minimum privs you have to grant is "CREATE SESSION" through this user connect to database.


regards
Taj

Previous Topic: Encryption in Oracle 9i Database
Next Topic: unable to connect using sys password
Goto Forum:
  


Current Time: Wed Dec 04 13:28:55 CST 2024