By, default oracle create two users when you create your database. they are called SYS ( with a password of " change on install" ) and SYSTEM ( with a password of "manager"). This users are called DataBase Administrator user ( DBA user).
user name password
SYS "Installation time"
SYSTEM "manager"
Create a user
DBA can create another user who can share the database.
Syntax:
CREATE USER USERNAME IDENTIFIED BY PASSWORD;
Ex.
Consider to create a user called Bob with a password of mypass, DBA issue the following command,
SQL > CREATE USER Bob IDENTIFIED BY mypass;
User created
SQL >
Note: It is important to note that username and password in Oracle are not case sensitive. The user and password can be uppercase or lower case. Oracle ensures only that the right combination of characters are sent across, not whether they are uppercase or lower case.
Ex.
SQL > connect Bob/mypass
Or
SQL > connect BOB/MYPASS
Then it will logon the Bob user.
DROP USER
If a DBA determines that a user should no longer have access to the database he or she can remove the user from the database using the DROP user command.
Syntax:
DROP USER USERNAME [ CASCADE ]
Ex.
Consider, DBA issue the following command to remove Susan from the database.
SQL > DROP USER Susan;
User Dropped.
SQL>
If Susan owed objects in the database, Then DBA receive CADE an error to go to Drop the Susan User.
SQL > Drop USER Susan;
DROP USER Susan
*
ERROR at line 1:
ORA-01922 : CASE must be specified to drop 'Susan'
The reason for the error is that oracle does not allow a user who owns database object to be removed because this orphans the objects. This is because the user's schema can't be separated from the user. If you want to drop Susan and all of the objects she owns, you can as the message indicates, specify the CASCADE option.
ALTER USER
In order to change another user's password by DBA and if DBA have been Granted the ALTER USER system privilege then user can change his or her own password using the given syntax.
Syntax:
ALTER USER USERNAME IDENTIFIED BY PASSWORD
Ex.
If Bob is logged into the database as the user Bob, then he can change his own password,
SQL > ALTER USER Bob IDENTIFIED BY newpass;
USER Altered.
SQL >
Now it will set new password "newpass" for Bob user.
GRANT AND ADMINISTERING USER PRIVILEGES
After you have created user accounts in Oracle, you need to enable those user to perform certain actions in the database or to access and manipulate objects in the database. This is accomplished through the granting and revoking of different privileges.
Oracle has two different types of privileges that can be granted -
1. System privileges.
2 Object privileges.
System privileges enable a user to perform certain database actions such as create a table or an index or event connect to the instance.
Object privileges enable a user to manipulate objects, such as read data through a view, execute a stored procedure, or change data in a table.
Generally, system privileges are granted to and revoked from users by the DBA, but object privileges are granted to and revoked from users by the owner of the object.
GRANT a system Privileges
The syntax for assigning system privileges is as follows,
Syntax:
GRANT Privilege1,privilege2,privilege3..... TO username1,username2,username3...... [ WITH ADMIN OPTION ]
As we can see by this syntax, it possible to grant multiple privileges to multiple users at the same time.
Ex.
If you as the DBA want to grant the CREATE SESSION privilege to Bob and Susan, you issue the following command,
SQL > GRANT CREATE SESSION TO Bob,Susan;
Grant Succeeded.
SQL >
Ex.
If DBA want to enable Bob to create tables, views, triggers, indexes, synonyms and sequences in his schema, you can issue the following command.
SQL > GRANT CREATE TABLE, CREATE VIEW, CRETE SYNONYM, CREATE SEQUENCE, CREATE TRIGGER, CREATE INDEX TO Bob;
Grant succeeded.
SQL>
THE WITH ADMIN OPTION privileges
DBA also has the option to enable the normal users to grant the same privilege to other users. If this is the result desired, the grant the privilege desired, the grantor can grant the privilege using the WITH ADMIN OPTION.
Ex.
If the DBA wants to enable Bob, who is the development manager to grant the privileges to create tables. Indexes and other database objects to other users, the DBA grants Bob the privilege WITH ADMIN OPTION as shown here:
SQL > GRANT CREATE TABLE, CREATE VIEW, CRETE SYNONYM, CREATE SEQUENCE, CREATE TRIGGER, CREATE PROCEDURE TO Bob WITH ADMIN OPTION;
Grant succeeded.
SQL >
Now if Bob wants to grant Susan the privilege [ only which privileges are given by DBA ] to create tables, sequences and synonym, Bob can issue the following command.
SQL > connect Bob/newpass;
Connected.
SQL > GRANT CREATE TABLE, CREATE SEQUENCE, CREATE SYNONYM TO Susan;
Grant succeeded.
SQL >
Determining system privilege granted
SQL > SELECT * FROM USER_SYS_PRIVS;
Show all the users who has system privileges and ADM option.
REVOKE a system privileges
If you do not want anyone to contain to have a system privilege granter to them. You can use the REVOKE command to remove the privilege granted.
Syntax:
REVOKE Privilege1,Privilege2,....... FROM Username1,Username2,.............
Ex.
If the DBA no longer wanted Bob to be able to create stored procedures in the database, the DBA can issue the following command,
SQL > REVOKE CREATE PROCEDURE FROM Bob;
REVOKED succeeded.
SQL >
Note: One side effect is the result of specifying WITH ADMIN OPTION at the time a system privilege is granted. Which the DBA may revoked the privilege granted to the user WITH ADMIN OPTION, if the user ( in this case, Bob) granted that privilege to other, it is not removed from those users that were granted the privilege.
OBJECT PRIVILEGES
The second type of privileges that can be granted to a user in Oracle are object privileges. Object privileges enable a user to manipulate data in the database or perform an action on an object, such as execute a stored procedure.
Syntax.
GRANT privilege1, privilege2 |ALL [Column1, Coloumn2,...] ON Objectname To User | role | PUBLIC [ WITH GRANT OPTION ];
The major different in the syntax between system and object privileges is that the keyword ON must be specified to determine which object the privileges apply to. Further more, object privilege for views and tables can also specify with column of the view or table they should be applied to. The keyword ALL specifies that all privileges that apply to the object should be granted. The privilege can be granted to user, role, or, the PUBLIC (Means all users in database ).
Ex.
To enable all users in the database to query the courses table.
SQL > GRANT ON Courses To PUBLIC;
Grant succeeded.
SQL>
Ex.
To enable the user Susan in the database to update the data field coursname of the table Courses,
SQL > GRANT UPDATE(coursname) ON Courses TO susan;
Ex.
Similarly to the WITH ADMIN OPTION system privilege, the WITH GRANT OPTION object privilege enable a user granted the privilege to grant it to some other user.
SQL > GRANT SELECT ON ClassEnrollment TO Bob WITH GRANT OPTION;
Grant succeeded.
SQL>
| Privilege | Granted On |
| SELECT INSERT UPDATE DELETE ALTER INDEX REFERENCE EXECUTE | Table, View, Sequence Table, View Table, View Table, View Table, Sequence Table Table Procedure, Function, Package |
Determining the object privileges granted
Ex.
SQL > SELECT * FROM USER_TAB_PRIVS_MADE
Lists the object privileges granted to other on objects in the user's schema. This includes privileges granted by the user to other and granted by users that have been assigned the privilege WITH GRANT OPTION.
SQL > SELECT * FROM USER_TAB_PRIVS_RECD;
Lists the object privileges that were granted to the user on objects in other schemas.
SQL > SELECT * FROM USER_COL_PRIVS_MADE;
Lists the object privileges made on columns of objects owned by the user.
SQL > SELECT * FROM USER_COL_PRIVS_RECD;
Lists the object privileges on columns of objects in other schemas granted to the user.
Syntax:
REVOKE Privilege1,privilege2,... | ALL (Column1,Xolumn2,..) NO Objectname FROM User | Role | PUBLIC [ CASCADE CONSTRAINTS ];
Ex.
To revoke the SELECT privilege grant to Bob on the ClassEnrollment table, the table owner executes the following command.
SQL > REVOKE SELECT ON ClassEnrollment FROM Bob;
Revoke succeeded.
SQL >
* If you compare this output, them you saw that it are revoked from that user and any other user that he granted them.
* An option of the REVOKE command for object privileges is CASCADE CONSTRAINTS. This option is required is those situation where a user has been granted REFERENCES permission on a table in your schema and he or she has used this privilege to create a table with a FOREIGN KEY constraint depending upon the table you own. Attempting to revoke the REFERENCE privileges an error.
Ex.
SQL > REVOKE REFERENCE ON Course FROM Bob CASCADE CONSTRAINTS;
Revoke succeeded.
SQL >
ROLE
Oracle provides a mechanism to group permissions together and then assign the whole group of permission to a user, this mechanism is called a ROLE.
A ROLE is a container that holes privileges. The main benefit of a ROLE is that it simplifies the process of granting privileges to users. To make the process efficient, a DBA creates a ROLE and then grants all of the privileges required by a user to perform a task to the ROLE.
CREATE AND GRANTING ROLE
* ROLE is create by DBA using the CREATE ROLE command, as shown in below,
SQL > CREATE ROLE OrderEntry;
Role create.
SQL >
* After the role has been created, the DBA or the owner of a database object can assign permissions to a role as following,
SQL > GRANT CREATE SESSION TO OrderEntry;
Grant succeeded.
SQL > connect student/oracle;
connected.
SQL > GRANT SELECT ON Student.courses TO OrderEntry.
Grant succeeded.
SQL > GRANT SELECT, INSERT, UPDATE ON Student.ClassEnrollment TO OrderEntry;
Grant succeeded.
SQL >
As shown by the preceding output both system privileges and object privileges can be granted to a role.
Now,
SQL > connect Bob/newpass;
Connected.
SQL > SELECT * FROM Student.ClassEnrollment;
Now, It will show a ERROR that "table or view does not exist".
SQL > connect system/manager;
connected.
SQL > GRANT OrderEntry TO Bob;
Grant succeeded.
SQL > connect Bob/newpass;
Connected.
SQL > SELECT * FROM Student.ClassEnrollment;
Now, it will show the table.
Determining privileges and role granted
SQL > SELECT * FROM USER_ROLE_PRIVS;
It will show all the role which have been granted to the user.
SQL> select * FROM ROLE_SYS_PRIVS;
Lists the system privileges that have been assigned to roles and available to the currently logged in user.
SQL > SELECT * FROM ROLE_TAB_PRIVS;
Lists the object privileges granted to the role on tables in the database. All of these privileges are available to the current user.
REVOKE ROLE Rolename FROM User | ROLE;
Ex.
To revoke the OrderEntry role from Bob, the DBA issue the following command.
SQL > REVOKE OrderEntry FROM Bob;
Revoke succeeded.
SQL >