DB User Accounts
DB Administrator accounts:
- owns DataDictionary
- OS user logged with SYSDBA privilege becomes SYS account
- can’t upgrade database to the newer version
- can’t do backup&recovery
- can manage users
- manage resource
- select catalog
User consists of unique username (<= 30 characters, alphanumeric $ and _) and authentication method (most comon password).
When using password we would create user as
CREATE USER username IDENTIFIED BY password;
Deleting user and all the objects he owns will be done with the following command
DROP USER username CASCADE;
When user is created he gets automatically default and temporary tablespace which is not optimal and we want to give users specific tablespace and certain amount on memory on it.
ALTER USER username QUOTA 10M ON tablespace_name;
- OPEN – open for business
- LOCKED or EXPIRED – unable to be used
List of all System and Object privileges HERE
We are granting privileges with GRANT, retrieving them with REVOKE and letting users grant those privileges on their own to other users with WITH GRANT command.
Special Admin privileges:
- can do anything in DB
- user becomes SYS
- OS admin groups get that privilege automatically (Win ORA_DBA users, *NIX DBA user group)
- no acces to data itself
- does backup, startup and shutdown of the DB
simplify administration. Role is a collection of privileges that can be given to as many users as we want.
GRANT rolename TO username;
Predefined roles are :
- DBA (SYS/SYSTEM)
- RESOURCE – can create tables and many pl/sql objects
- CONNECT – just establishing connection with the DB
More on Profiles could be read HERE.
Assigning a profile to the user
ALTER USER username PROFILE profile_name;
*** Good link on this topic LINK