Home » Other » Training & Certification » Schemas and Users
Schemas and Users [message #355381] Thu, 23 October 2008 19:58 Go to next message
dland
Messages: 2
Registered: October 2008
Junior Member
Like many beginners, I find the whole schema vs. user thing a bit of confusing. Schemas are kind of like users and the two terms are often used interchangeably, but apparently they aren't quite the same thing. I have some specific questions:

1. Can you have more than one user for a schema? If not, how can you connect to the database with different privileges? Doesn't this become a security problem? Is it always up to the application code to make sure that users can't do things they aren't supposed to do?

2. Can you have one user that can access multiple schemas? E.g. can Joe access both the DataWarehouse and OnlineBookStore schemas?

3. Can you have a user with a different name than the schema (e.g. schema name = 'DataWarehouse' and user name = 'Joe')?

Thanks,
Dave
Re: Schemas and Users [message #355416 is a reply to message #355381] Thu, 23 October 2008 23:58 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator

  1. No, you can't have more than one user for a schema. Think of the issue like this: schema = user + all its objects (tables, procedures, functions, views, ...).
    Connecting with different privileges? I'm not sure what you mean by that, but nothing stops you from creating many users in the same database, each of them with different privileges, each of them operating with the same data. In order to enable that, owner has to grant certain privileges to all of these users.
  2. Certainly; GRANT is again the keyword.
  3. No; username is always the same as its schema name.
Re: Schemas and Users [message #355430 is a reply to message #355416] Fri, 24 October 2008 00:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Answers to 1. and 3. could be different if you use entreprise user and shared schemas.

Regards
Michel
Re: Schemas and Users [message #355490 is a reply to message #355416] Fri, 24 October 2008 08:05 Go to previous messageGo to next message
dland
Messages: 2
Registered: October 2008
Junior Member
Thanks for the reply. The security issue I was talking about goes something like this: Say you have some sort of application. The application supports multiple different roles with different privileges. Your standard user can only read data, no editing. A super user can do any operations on the data they wish including insert, update, and delete. If you were using SQL Server, you could set up two database users, one called "admin" with the ability to modify tables, and one called "readonly". Then you could have the application super user always connect to SQL Server as the "admin" database user and the standard user connect as the "readonly" database user. So if somehow the standard user were to try to delete some data, SQL Server wouldn't allow it because they are connecting to it as a user without that privilege. It's just another layer of security.

How would you achieve something like that in Oracle? Or is it just up to the application to make sure than standard users never send INSERT/UPDATE/DELETE commands to Oracle?
Re: Schemas and Users [message #355491 is a reply to message #355490] Fri, 24 October 2008 08:10 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
By default, a user has no privileges to see or alter any data from another user.
You could create two roles: one readonly and one admin-role.
Grant select on the application-schema's objects to the readonly role; grant whatever you want on the application-schema's object to the admin role.
Then assign the proper role to whatever users you have.
Re: Schemas and Users [message #355500 is a reply to message #355490] Fri, 24 October 2008 08:47 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Start with:
Database Concepts
Chapter 1 Introduction to the Oracle Database
Section Oracle Database Features
Subsection Overview of Security Features

and then follow the link to Chapter 20, "Database Security".

Regards
Michel

Previous Topic: HI ALL I'M FROM EGYPT
Next Topic: Database security
Goto Forum:
  


Current Time: Thu Mar 28 03:55:25 CDT 2024