User Management (ORACLE DBA)
Users Managing is an important area of database administration. without users, there can be no database change.
There are various types of users which have different responsibilities and rights. The main categories are two user accounts are automatically created with the database and granted the DBA role. These two user accounts are
- SYS {initial password: CHANGE_ON_INSTALL}
- SYSTEM {initial password: MANAGER}
When new users in Oracle are added, some rights are assigned to that user so that actions are performed on the database either directly or through roles. There are two types of privileges given to a user:
- System privileges through which the user can manage the performance of database actions.
- Object privileges which allow access to objects, i.e. {tables, table columns, indexes, synonyms, procedures,} etc.
Methods to add new users/ Creating new users in database :-
SQL> CREATE USER user_name IDENTIFIED BY password;
SQL> CREATE USER user_name IDENTIFIED BY user_name;
SQL> CREATE USER user_name IDENTIFIED {BY password | EXTERNALLY}
[DEFAULT TABLESPACE tablespace name] [TEMPORARY TABLESPACE tablespace name] [ { QUOTA {n [K|M] | UNLIMITED} ON tablespace name } [, ... ] ] [PROFILE profile]}.
Altering User :-
SQL> ALTER USER user_name IDENTIFIED {EXTERNALLY | PASSWD}
[ DEFAULT TABLESPACE Tablespace_name
TEMPORARY TABLESPACE temp_tablespace_name
QUOTA 200M ON tablespace_name
QUOTA 0 ON tablesapce1_name
PROFILE profile_name;
Dropping User :-
Don't attempt to drop the SYS or SYSTEM users.
SQL> DROP USER user_name CASCADE;
Note : If any objects linked with user then use CASCADE otherwise just user drop user user_name cmd.
Grant / Revoke Privileges to USER :
Privilege is a permission/right to perform a transaction.
Some examples of privilege : Connect to the database (create a session) ,Create a table ,Select rows from another user's table, Execute another user's stored procedure
There are two distinct categories of privileges:
- System privileges
- Schema object privileges
SQL> Revoke CREATE INDEX or CREATE TABLE from username;
Grant Privileges on Tables :
Syntax for granting privileges on a table is:
SQL> grant privileges on object to username;
For example, if you wanted to grant select, insert, update, and delete privileges on a table called suppliers to a user name smithj, you would execute the following statement:
SQL> grant select, insert, update, delete on suppliers to username;
You can also use the all keyword to indicate that you wish all permissions to be granted. For example:
SQL> grant all on suppliers to username;
If you wanted to grant select access on your table to all users, you could grant the privileges to the public keyword. For example:
SQL> grant select on suppliers to public;
Revoke Privileges on Tables :
Once you have granted privileges, you may need to revoke some or all of these privileges. To do this, you can execute a revoke command. You can revoke any combination of select, insert, update, delete, references, alter, and index.
Syntax for revoking privileges on a table is:
SQL> revoke privileges on object from username;
For example, if you wanted to revoke delete privileges on a table called suppliers from a user named anderson, you would execute the following statement:
SQL> revoke delete on suppliers from username;
If you wanted to revoke all privileges on a table, you could use the all keyword. For example:
SQL> revoke all on suppliers from username;
If you had granted privileges to public (all users) and you wanted to revoke these privileges, you could execute the following statement:
SQL> revoke all on suppliers from public;
Managing Oracle Roles :
A Role is Set Privileges. Group Of privileges is called one role.Syntax for creating a role
SQL> create role select_role_db;
SQL> grant select on dept, emp, sales, bonus to select_role_db;
Granting role to user
SQL> grant select_role_db to username;
Revoking Role from User
SQL> revoke select_role_db from username;
Managing Oracle Profiles :
Oracle Profile means password and Resource limitations of Database.
Creating Profile
SQL> CREATE PROFILE name_profile LIMIT PASSWORD_REUSE_MAX 10 PASSWORD_REUSE_TIME 30;
Example of Setting Profile Resource Limits :
SQL> CREATE PROFILE name1_user LIMIT
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL 3000
CONNECT_TIME 45
LOGICAL_READS_PER_SESSION DEFAULT
LOGICAL_READS_PER_CALL 1000
PRIVATE_SGA 15K
COMPOSITE_LIMIT 5000000;
Examples Setting Profile Password Limits
SQL> CREATE PROFILE app_user2 LIMIT
FAILED_LOGIN_ATTEMPTS 5
PASSWORD_LIFE_TIME 60
PASSWORD_REUSE_TIME 60
PASSWORD_REUSE_MAX 5
PASSWORD_VERIFY_FUNCTION verify_function
PASSWORD_LOCK_TIME 1/24
PASSWORD_GRACE_TIME 10;
HTML Comment Box is loading comments...