Solved

New User in Oracle

Posted on 2011-02-15
8
415 Views
Last Modified: 2012-06-27
We have a oracle database and i need to set up a user with username 'acct_Dani' and password 'gT54rlo' and make it so this account only has view rights (in other words cant use update, replace or delete)

What is the sql code that i need to use to run this.

Also i know i can use

select * from all_users
-- view all users

Select * from SESSION_PRIVS
-- view all possible privelidges

but how do i view all priveledges for a specfic user
0
Comment
Question by:Brant Snow
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
from a SQL prompt:
create user acct_Dani identified by gT54rlo default tablespace users temporary tablespace temp;

grant create session to acct_Dani;

For the select only, I would create a role and grant select to the individual objects then grant that role to acct_Dani.

the only other way is granting 'select any' and that opens the database up because the user can select system tables, source code, etc...

>>but how do i view all priveledges for a specfic user

This is pretty hard to actually do in Oracle.  There are several views you need because you can have privileges, roles all granted to one another.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
More on permissions:

There is DBA_TAB_PRIVS that will show explicit grants to tables, DBA_ROLE_PRIVS to show roles granted to a user,  DBA_SYS_PRIVS to show system privs (create, drop, etc...) granted to a user.

Then there's ROLE_TAB_PRIVS, ROLE_SYS_PRIVS to show what is granted to a role.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
Oracle does not give us a way to explicitly limit an account so it "only has view rights (in other words can't use update, replace or delete)".

But, when you create a new account, it will only include those priviliges (like: insert, update and delete) for tables that the account owns.  If you do not create any tables in that new user account (schema) then the new account will not be able to do those actions in tables that belong to other users.  Also, that new account will only be able to read (select) from tables in other schemas if those schema owners explicitly grant this new owner permission to select from their tables.

0
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
While most DBAs would never do this, be aware of privileges granted to PUBLIC.  If the insert/update/delete privilege is granted to PUBLIC, then every user has the privilege.  Some third party applications where the security is enforced at the application level and not the database level grant all privileges to PUBLIC.  It is a bad security design, but it is out there.  People have also been known to grant things to PUBLIC just to make a problem go away, rather than figure it out.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 4

Author Comment

by:Brant Snow
Comment Utility
slightwv you say the best would be to create a role and assign it select privledges correct, how do i do that.

and if i want to add a user to say myDatabase so you wrote

create user acct_Dani identified by gT54rlo default tablespace users temporary tablespace temp;

grant create session to acct_Dani;

i understand that except do i need to insert the name of the table within the {tablespace}?

How could i create a role and assign it to the user?
0
 
LVL 34

Assisted Solution

by:johnsone
johnsone earned 175 total points
Comment Utility
To create a role:

CREATE ROLE READ_ONLY_ROLE;    -- You can use any name you want.

To assign it to a user:

GRANT READ_ONLY_ROLE TO <user>;

You grant privileges to the role the same way you grant them to a user.

GRANT SELECT ON <table> TO READ_ONLY_ROLE;
0
 
LVL 34

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 75 total points
Comment Utility
"do i need to insert the name of the table within the {tablespace}?"

No.  When you create a user in Oracle you do not provide a table name.  You should specify two tablespace names as suggested: a default one (in case the user ever does create a table); and a temporary one (that Oracle will use if/when this user executes queries that require temporary space on disk for sorts, etc.).

0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
Comment Utility
>>be aware of privileges granted to PUBLIC.

Thanks.  I keep forgetting about PUBLIC.

>>slightwv you say the best would be to create a role and assign it select privledges correct, how do i do that.

johnsone provided that for me in http:#a34900572.

You need to grant select on every table/view necessary.  You can have SQL generate SQL as a shortcut.

sql script:
--------------------------------
set pages 0
set feedback off
set lines 1000
set trimspool on
spool myscript.sql
select 'grant select on SOME_SCHEMA.' || table_name || ' to READ_ONLY_ROLE;' from dba_tables where owner='SOME_SCHEMA';
spool off

@myscript
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now