8. SQL Statements : CREATE USER : Create User Examples
 
Share this page                  
Create User Examples
1. Create a new user, specifying group and privileges.
CREATE USER bspring WITH
   GROUP = publishing,
   PRIVILEGES = (CREATEDB, SECURITY);
2. Create a new user, group and no privileges.
CREATE USER barney WITH
   GROUP = sales,
   NOPRIVILEGES;
3. Define user expiration date.
CREATE USER bspring
   WITH EXPIRE_DATE = '6-jun-2015'
4. Define an expiration date relative to the date the statement is executed.
CREATE USER bspring
   WITH EXPIRE_DATE = '1 month'
5. Specify no expiration date for a user.
CREATE USER bspring WITH NOEXPIRE_DATE
6. Create a user with a password.
CREATE USER bspring WITH PASSWORD='mypassword';
7. Create a user with several privileges, and a smaller set of default privileges.
CREATE USER bspring
   WITH PRIVILEGES=(CREATEDB, SECURITY, TRACE),
     DEFAULT_PRIVILEGES = (TRACE);
8. Specify a profile for a particular user.
CREATE USER bspring WITH PROFILE = dbop
where dbop is an existing profile.
9. Specify a user with an externally verified password.
CREATE USER bspring
   WITH EXTERNAL_PASSWORD;
10. Create a user that requires authentication through the DBMS (rather than through the operating system, installation passwords, or Kerberos). Allow the user to change his password:
CREATE USER fred
   WITH PASSWORD='secret',
   PRIVILEGES = (CHANGE_PASSWORD), DBMS_AUTHENTICATION='REQUIRED';