| Schemas and Users [message #355381] |
Thu, 23 October 2008 19:58  |
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  |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
- 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.
- Certainly; GRANT is again the keyword.
- No; username is always the same as its schema name.
|
|
|
|
|
|
| Re: Schemas and Users [message #355490 is a reply to message #355416] |
Fri, 24 October 2008 08:05 |
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 |
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
