Oracle DBA FAQ - Managing Oracle User Accounts, Schema and Privileges

Home >> FAQs/Tutorials >> Oracle DBA FAQ

By: FYIcenter.com

Part: 1 2 3 4 5 6

A collection of 23 FAQs on Oracle user account, schema and privileges. Clear answers are provided with tutorial exercises on creating user accounts, granting privileges for session connections, granting privileges for creating tables and inserting rows. Topics included in this FAQ are:

What Is a User Account?
What Is the Relation of a User Account and a Schema?
What Is a User Role?
What Are the System Predefined User Roles?
What Are Internal User Account?
How To Connect to the Server with User Account: SYS?
How To Use Windows User to Connect to the Server?
How To List All User Accounts?
How To Create a New User Account?
How To Change User Password?
How To Delete a User Account?
What Privilege Is Needed for a User to Connect to Oracle Server?
How To Grant CREATE SESSION Privilege to a User?
How To Revoke CREATE SESSION Privilege from a User?
How To Lock and Unlock a User Account?
What Privilege Is Needed for a User to Creat Tables?
How To Assign a Tablespace to a Users?
What Privilege Is Needed for a User to Creat Views?
What Privilege Is Needed for a User to Creat Indexes?
What Privilege Is Needed for a User to Query Tables in Another Schema?
What Privilege Is Needed for a User to Insert Rows to Tables in Another Schema?
What Privilege Is Needed for a User to Delete Rows from Tables in Another Schema?
How To Find Out What Privileges a User Currently Has?

What Is a User Account?

A user account is identified by a user name and defines the user's attributes, including the following:

Password for database authentication
Privileges and roles
Default tablespace for database objects
Default temporary tablespace for query processing work space

What Is the Relation of a User Account and a Schema?

User accounts and schemas have a one-to-one relation. When you create a user, you are also implicitly creating a schema for that user. A schema is a logical container for the database objects (such as tables, views, triggers, and so on) that the user creates. The schema name is the same as the user name, and can be used to unambiguously refer to objects owned by the user.

What Is a User Role?

A user role is a group of privileges. Privileges are assigned to users through user roles. You create new roles, grant privileges to the roles, and then grant roles to users.

What Are the System Predefined User Roles?

Oracle 10g XE comes with 3 predefined roles:

CONNECT - Enables a user to connect to the database. Grant this role to any user or application that needs database access.
RESOURCE - Enables a user to create certain types of schema objects in his own schema. Grant this role only to developers and to other users that must create schema objects. This role grants a subset of the create object system privileges.
DBA - Enables a user to perform most administrative functions, including creating users and granting privileges; creating and granting roles; creating and dropping schema objects in other users' schemas; and more. It grants all system privileges, but does not include the privileges to start up or shut down the database. It is by default granted to user SYSTEM.

What Are Internal User Account?

An internal user account is a system predefined user account. Oracle 10g XE comes with a number of internal accounts:

SYSTEM - This is the user account that you log in with to perform all administrative functions other than starting up and shutting down the database. SYSTEM is automatically created when you install the server. It's password is the one you specified during the installation process.
SYS - This is another user account automatically created when you install the server. It's password is the one you specified during the installation process. All base tables and views for the database data dictionary are stored in the SYS schema. So avoid log in as user SYS as much as possible to reduce the risk of damaging those important data objects. User SYSTEM is preferred for all administrative tasks except starting up and shutting down.
Other internal user accounts - Other special user accounts are predefined for special purposes. For example, CTXSYS is a special user account used by the Oracle Text product.

How To Connect to the Server with User Account: SYS?

SYS is a very special user account. It has been associated with the highest privilege call SYSDBA. Normally, you should not connect to the server with SYS. But if you want to use it, you need to use a special connect command:

>cd (OracleXE home directory)
>.\bin\sqlplus /nolog

SQL> connect SYS/fyicenter AS SYSDBA
Connected.

SQL> quit

Note that the "/nolog" option is used to start SQL*Plus without login immediately. A special form of the "connect" command is used to include the user name, password, and the privilege in the same line.

You can not log in with SYS without SYSDBA privilege.

(Continued on next part...)

Part: 1 2 3 4 5 6

More...