Creating a user
Like table names and columns, a user name must follow the same rules. There is one minor difference. A user name can contain a dollar sign.
CREATE USER username
[ IDENTIFIED BY password ]
[ PASSWORD EXPIRE ]
The syntax for creating a user is pretty straight forward. The PASSWORD EXPIRE is used for when the user logs on for the first time and makes them to change their password. Once a user has been created they are not allowed to log into the system because they have no privileges at all.
Privileges
There are two types of privileges, one is system privilege and the other is object privilege. The system privileges allow a user to connect to the data base and perform DDL operations. Object privileges allow users to perform DML operations.
There are about 200 privileges in Oracle 10g. These privileges can be found in the system_privilege_map table.
GRANT systemprivilege [, systemprivilege, … ]
TO username | rolename [, username | rolename, …]
[ WITH ADMIN OPTION ];
When a user creates an object that user has all the privileges for that object. While another user does not have any privileges unless he is explicitly assigned privileges. There are a total of 15 object privileges in Oracle 10g. To grant an object privilege the syntax is as follows. Note the TO clause in the statement; this is a dead give away as to what kind of grant it is.
GRANT { objectprivilege | ALL } [ ( columnename ),
objectprivilege ( columnname ) ]
ON objectname
TO { username | rolename | PUBLIC }
[ WITH GRANT OPTION ];
An object privilege or the ALL keyword must be used after the GRANT keyword. You must grant the privilege to at least one user or role. Multiple users and roles can be listed by using a comma to separate them. To grant a privilege to all the users, the PUBLIC keyword is used. Keep in mind that you can’t use the WITH GRANT OPTION clause when granting privileges to roles.
Roles
To make the life of the DBA a bit easier and to make a lot more sense, Oracle implemented something called roles. A role is a collection of privileges. Using a role is very straight forward. To create a role:
CREATE ROLE rolename;
That’s all you need to do to create a role. Next we need to assign roles to users:
GRANT rolename [, rolename ]
TO username | rolename [, username | rolename ];
User’s who have more than one role can have a default role set for them. More than one role can be set as their default role as well.
ALTER USER username
DEFAULT ROLE
( rolename | ALL [ EXCEPT role1, role2, … ] | NONE )
The ALL clause uses the EXCEPT keyword which you can use to specify which roles you want to exclude from the list of ALL roles. The NONE clause is telling Oracle that you don’t want this particular user to have a default role. When that user logs in, they must manually set their role by:
SET ROLE rolename;
Revoking
Revoking system privileges, object privileges, and roles are very straight forward. To revoke system and object privileges, respectfully:
REVOKE systemprivilege [, systemprivilege… ]
FROM username | rolename;
REVOKE objectprivilege [, objectprivilege…]
ON objectname
FROM username | rolename;
You can remove roles from users as well as remove privileges from roles. To remove a role from a user:
REVOKE rolename
FROM username | rolename;
To completely drop a role form the database just issue the drop command like this:
DROP ROLE rolename;
Dropping a user from the database has the same setup:
DROP USER username