1. What Is a User Account?
A user account is identified by a user name and defines 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
2. 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.
3. 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.
4. 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.
5. 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.
6. 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.
7. How To Use Windows User to Connect to the Server?
During the installation process, 10g XE will create a special Windows user group called ORA_DBA, and put your Windows user into this group. Any Windows users in this group can be connected to Oracle server with SYSDBA privilege without any Oracle server user account. This process is called connecting the server as SYSDBA with OS Authentication. Here is how to do this with a special form of the "connect" command:
(Log in with the same user you used to install 10g XE)
>cd (OracleXE home directory)
>.\bin\startdb
>.\bin\sqlplus /nolog
SQL> connect / AS SYSDBA
Connected.
SQL> quit
So if "connect" is used without user name and password, the current Windows user will be trusted if he/she is in the ORA_DBA user group on the Windows system.
8. How To List All User Accounts?
User accounts can be accessed through a system view called ALL_USERS. A simple SELECT statement can be used to get a list of all user accounts. Try the following script:
>.\bin\sqlplus /nolog
SQL> connect SYSTEM/fyicenter
Connected.
SQL> SELECT * FROM ALL_USERS;
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
FLOWS_020100 35 07-FEB-06
FLOWS_FILES 34 07-FEB-06
HR 33 07-FEB-06
MDSYS 32 07-FEB-06
ANONYMOUS 28 07-FEB-06
XDB 27 07-FEB-06
CTXSYS 25 07-FEB-06
DBSNMP 23 07-FEB-06
TSMSYS 20 07-FEB-06
DIP 18 07-FEB-06
OUTLN 11 07-FEB-06
SYSTEM 5 0 7-FEB-06
SYS 0 07-FEB-06
9. How To Create a New User Account?
If you want to create a new user account, you can log in as SYSTEM and use the CREATE USER command as shown in the following example:
>.\bin\sqlplus /nolog
SQL> connect SYSTEM/fyicenter
Connected.
SQL> CREATE USER DEV IDENTIFIED BY developer ACCOUNT UNLOCK;
User created.
Note that CREATE is a SQL statement, so you need to terminate it with ";". This command creates a user called "DEV", with a password of "developer". You can test this account by log in with DEV from SQL*Plus.
10. How To Change User Password?
If you want to change a user's password, you can log in as SYSTEM and use the ALTER USER command as shown in the following example:
>.\bin\sqlplus /nolog
SQL> connect SYSTEM/fyicenter
Connected.
SQL> ALTER USER DEV IDENTIFIED BY beginner;
User altered.
Note that ALTER is SQL statement, so you need to terminate it with ";". This command resets DEV's password to "beginner".
11. How To Delete a User Account?
If you want to delete a user account and its associated schema, you can log in as SYSTEM and use the DROP USER command as shown in the following example:
>.\bin\sqlplus /nolog
SQL> connect SYSTEM/fyicenter
Connected.
SQL> DROP USER DEV CASCADE;
User dropped.
SQL> CREATE USER DEV IDENTIFIED BY developer ACCOUNT UNLOCK;
User created.
Note that CASCADE tells the server drop the associated schema.
12. What Privilege Is Needed for a User to Connect to Oracle Server?
Oracle deny connection to users who has no CREATE SESSION privilege. Try the following tutorial exercise, you will find out how Oracle denies connection:
>.\bin\sqlplus /nolog
SQL> connect SYSTEM/fyicenter
SQL> CREATE USER DEV IDENTIFIED BY developer ACCOUNT UNLOCK;
User created.
SQL> disconnect
SQL> CONNECT DEV/developer
ORA-01045: user DEV lacks CREATE SESSION privilege;
logon denied
Oracle error message is pretty clear.
13. How To Grant CREATE SESSION Privilege to a User?
If you want give a user the CREATE SESSION privilege, you can use the GRANT command. The following tutorial exercise shows you how to grant DEV the privilege to connect to the server:
>.\bin\sqlplus /nolog
SQL> connect SYSTEM/fyicenter
SQL> GRANT CREATE SESSION TO dev;
Grant succeeded.
SQL> disconnect
SQL> CONNECT DEV/developer
Connected.
14. How To Revoke CREATE SESSION Privilege from a User?
If you take away the CREATE SESSION privilege from a user, you can use the REVOKE command as shown in the following example script:
>.\bin\sqlplus /nolog
SQL> connect SYSTEM/fyicenter
SQL> REVOKE CREATE SESSION FROM dev;
Revoke succeeded.
SQL> GRANT CREATE SESSION TO dev;
Grant succeeded.
This script restored the CREATE SESSION privilege to user "dev", so you can continue other example scripts below.
15. How To Lock and Unlock a User Account?
If you want to lock a user account for a short period of time, and unlock it later, you can use the ALTER USER ... ACCOUNT command. The following sample script shows how to use this command:
>.\bin\sqlplus /nolog
SQL> connect SYSTEM/fyicenter
SQL> ALTER USER dev ACCOUNT LOCK;
User altered.
SQL> disconnect
SQL> CONNECT DEV/developer
ORA-28000: the account is locked
SQL> disconnect
SQL> connect SYSTEM/fyicenter
SQL> ALTER USER dev ACCOUNT UNLOCK;
User altered.
SQL> disconnect
SQL> CONNECT DEV/developer
Connected.
16. What Privilege Is Needed for a User to Create Tables?
To be able to create tables in a user's own schema, the user needs to have the CREATE TABLE privilege, or the CREATE ANY TABLE privilege, which is more powerful, and allows the user to create tables in other user's schema. The following tutorial exercise gives you a good example on CREATE TABLE privilege:
>.\bin\sqlplus /nolog
SQL> CONNECT DEV/developer
SQL> CREATE TABLE fyi (id NUMBER);
ORA-01031: insufficient privileges
SQL> disconnect
SQL> connect SYSTEM/fyicenter
SQL> GRANT CREATE TABLE TO dev;
Grant succeeded.
SQL> disconnect
SQL> CONNECT DEV/developer
SQL> CREATE TABLE fyi (id NUMBER);
ORA-01950: no privileges on tablespace 'SYSTEM'
The above error message tells that user "dev" is not allowed to use the tablespace "SYSTEM". See the next question for answers.
17. How To Assign a Tablespace to a Users?
When you create a new user, Oracle will assign the SYSTEM tablespace to the user by default. If you want to change this, you can assign a different table space to a user using the ALTER USER command. The following tutorial exercise changes user dev's default tablespace, and assigns 4MB of space to dev:
>.\bin\sqlplus /nolog
SQL> CONNECT DEV/developer
SQL> ALTER USER dev DEFAULT TABLESPACE USERS;
User altered.
SQL> ALTER USER dev QUOTA 4M ON USERS;
User altered.
SQL> disconnect
SQL> CONNECT DEV/developer
SQL> CREATE TABLE fyi (id NUMBER);
Table created.
SQL> DROP TABLE fyi;
Table dropped.
SQL> CREATE TABLE fyi (id NUMBER);
Table created.
As you can see, "dev" can create and drop tables now. You can also let "dev" to create tables in any tablespace without any restriction by granting him the UNLIMITED TABLESPACE system privilege.
18. What Privilege Is Needed for a User to Create Views?
To be able to create views in a user's own schema, the user needs to have the CREATE VIEW privilege, or the CREATE ANY VIEW privilege, which is more powerful, and allows the user to create views in other user's schema. The following tutorial exercise gives you a good example on CREATE VIEW privilege:
>.\bin\sqlplus /nolog
SQL> CONNECT DEV/developer
SQL> CREATE VIEW fyi_view AS SELECT * FROM fyi;
ORA-01031: insufficient privileges
SQL> disconnect
SQL> connect SYSTEM/fyicenter
SQL> GRANT CREATE VIEW TO dev;
Grant succeeded.
SQL> disconnect
SQL> CONNECT DEV/developer
SQL> CREATE VIEW fyi_view AS SELECT * FROM fyi;
View created.
SQL> DROP VIEW fyi_view;
View dropped.
SQL> CREATE VIEW fyi_view AS SELECT * FROM fyi;
View created.
As you can see, "dev" can create and drop views now.
19. What Privilege Is Needed for a User to Create Indexes?
For a user to create indexes, he/she needs the same privilege as the creating tables. Just make sure he/she has the CREATE TABLE privilege. The following tutorial exercise gives you a good example on creating view privilege:
>.\bin\sqlplus /nolog
SQL> connect SYSTEM/fyicenter
SQL> GRANT CREATE TABLE TO dev;
Grant succeeded.
SQL> disconnect
SQL> CONNECT DEV/developer
SQL> CREATE INDEX fyi_index ON fyi(id);
Index created.
SQL> DROP INDEX fyi_index;
Index dropped.
SQL> CREATE INDEX fyi_index ON fyi(id);
Index created.
This exercise assumes that you followed previous exercises in the FAQ collection.
20. What Privilege Is Needed for a User to Query Tables in Another Schema?
For a user to run queries (SELECT statements) on tables of someone else's schema, he/she needs the SELECT ANY TABLE privilege. The following tutorial exercise gives you a good example of granting "dev" to query tables in "hr" schema:
>.\bin\sqlplus /nolog
SQL> CONNECT DEV/developer
SQL> SELECT COUNT(*) FROM hr.employees;
ORA-01031: insufficient privileges
SQL> disconnect
SQL> connect SYSTEM/fyicenter
SQL> GRANT SELECT ANY TABLE TO dev;
Grant succeeded.
SQL> disconnect
SQL> CONNECT DEV/developer
SQL> SELECT COUNT(*) FROM hr.employees;
COUNT(*)
----------
107
As you can see, "dev" can query tables in any schema now.
You also need to remember that table name must be prefixed with the schema name (same as owner user name).
21. What Privilege Is Needed for a User to Insert Rows to Tables in Another Schema?
For a user to insert rows into tables of someone else's schema, he/she needs the INSERT ANY TABLE privilege. The following tutorial exercise gives you a good example of granting "dev" to insert rows in "hr" schema:
>.\bin\sqlplus /nolog
SQL> CONNECT DEV/developer
SQL> INSERT INTO hr.jobs
VALUES ('DV.FYI', 'Dev FYI Consultant', 7700, 8800);
ORA-01031: insufficient privileges
SQL> disconnect
SQL> connect SYSTEM/fyicenter
SQL> GRANT INSERT ANY TABLE TO dev;
Grant succeeded.
SQL> disconnect
SQL> CONNECT DEV/developer
SQL> INSERT INTO hr.jobs
VALUES ('DV.FYI', 'Dev FYI Consultant', 7700, 8800);
1 row created.
As you can see, "dev" can insert rows in any schema now. But you should be careful when giving this privilege to a regular developer.
22. What Privilege Is Needed for a User to Delete Rows from Tables in Another Schema?
For a user to delete rows from tables of someone else's schema, he/she needs the DELETE ANY TABLE privilege. The following tutorial exercise gives you a good example of granting "dev" to delete rows in "hr" schema:
>.\bin\sqlplus /nolog
SQL> CONNECT DEV/developer
SQL> DELETE FROM hr.jobs WHERE job_id = 'DV.FYI';
ORA-01031: insufficient privileges
SQL> disconnect
SQL> connect SYSTEM/fyicenter
SQL> GRANT DELETE ANY TABLE TO dev;
Grant succeeded.
SQL> disconnect
SQL> CONNECT DEV/developer
SQL> DELETE FROM hr.jobs WHERE job_id = 'DV.FYI';
1 row deleted.
As you can see, "dev" can delete rows in any schema now. But you should be careful when giving this privilege to a regular developer.
23. How To Find Out What Privileges a User Currently Has?
Privileges granted to users are listed in two system views: DBA_SYS_PRIVS, and USER_SYS_PRIVS. You can find out what privileges a user currently has by running a query on those views as shown in the tutorial exercise below:
>.\bin\sqlplus /nolog
SQL> CONNECT DEV/developer
SQL> SELECT username, privilege FROM USER_SYS_PRIVS;
USERNAME PRIVILEGE
------------------------- ----------------------
DEV SELECT ANY TABLE
DEV INSERT ANY TABLE
DEV CREATE SESSION
DEV CREATE VIEW
DEV DELETE ANY TABLE
DEV CREATE ANY TABLE
SQL> disconnect
SQL> connect SYSTEM/fyicenter
SQL> GRANT DELETE ANY TABLE TO dev;
Grant succeeded.
SQL> SELECT GRANTEE, PRIVILEGE FROM DBA_SYS_PRIVS
WHERE GRANTEE = 'HR';
GRANTEE PRIVILEGE
------------------------- -----------------------
HR CREATE VIEW
HR UNLIMITED TABLESPACE
HR DEBUG CONNECT SESSION
HR CREATE DATABASE LINK
HR CREATE SEQUENCE
HR CREATE SESSION
HR DEBUG ANY PROCEDURE
HR ALTER SESSION
HR CREATE SYNONYM
Looks like "hr" has move privileges than "dev".
A user account is identified by a user name and defines 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
2. 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.
3. 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.
4. 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.
5. 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.
6. 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.
7. How To Use Windows User to Connect to the Server?
During the installation process, 10g XE will create a special Windows user group called ORA_DBA, and put your Windows user into this group. Any Windows users in this group can be connected to Oracle server with SYSDBA privilege without any Oracle server user account. This process is called connecting the server as SYSDBA with OS Authentication. Here is how to do this with a special form of the "connect" command:
(Log in with the same user you used to install 10g XE)
>cd (OracleXE home directory)
>.\bin\startdb
>.\bin\sqlplus /nolog
SQL> connect / AS SYSDBA
Connected.
SQL> quit
So if "connect" is used without user name and password, the current Windows user will be trusted if he/she is in the ORA_DBA user group on the Windows system.
8. How To List All User Accounts?
User accounts can be accessed through a system view called ALL_USERS. A simple SELECT statement can be used to get a list of all user accounts. Try the following script:
>.\bin\sqlplus /nolog
SQL> connect SYSTEM/fyicenter
Connected.
SQL> SELECT * FROM ALL_USERS;
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
FLOWS_020100 35 07-FEB-06
FLOWS_FILES 34 07-FEB-06
HR 33 07-FEB-06
MDSYS 32 07-FEB-06
ANONYMOUS 28 07-FEB-06
XDB 27 07-FEB-06
CTXSYS 25 07-FEB-06
DBSNMP 23 07-FEB-06
TSMSYS 20 07-FEB-06
DIP 18 07-FEB-06
OUTLN 11 07-FEB-06
SYSTEM 5 0 7-FEB-06
SYS 0 07-FEB-06
9. How To Create a New User Account?
If you want to create a new user account, you can log in as SYSTEM and use the CREATE USER command as shown in the following example:
>.\bin\sqlplus /nolog
SQL> connect SYSTEM/fyicenter
Connected.
SQL> CREATE USER DEV IDENTIFIED BY developer ACCOUNT UNLOCK;
User created.
Note that CREATE is a SQL statement, so you need to terminate it with ";". This command creates a user called "DEV", with a password of "developer". You can test this account by log in with DEV from SQL*Plus.
10. How To Change User Password?
If you want to change a user's password, you can log in as SYSTEM and use the ALTER USER command as shown in the following example:
>.\bin\sqlplus /nolog
SQL> connect SYSTEM/fyicenter
Connected.
SQL> ALTER USER DEV IDENTIFIED BY beginner;
User altered.
Note that ALTER is SQL statement, so you need to terminate it with ";". This command resets DEV's password to "beginner".
11. How To Delete a User Account?
If you want to delete a user account and its associated schema, you can log in as SYSTEM and use the DROP USER command as shown in the following example:
>.\bin\sqlplus /nolog
SQL> connect SYSTEM/fyicenter
Connected.
SQL> DROP USER DEV CASCADE;
User dropped.
SQL> CREATE USER DEV IDENTIFIED BY developer ACCOUNT UNLOCK;
User created.
Note that CASCADE tells the server drop the associated schema.
12. What Privilege Is Needed for a User to Connect to Oracle Server?
Oracle deny connection to users who has no CREATE SESSION privilege. Try the following tutorial exercise, you will find out how Oracle denies connection:
>.\bin\sqlplus /nolog
SQL> connect SYSTEM/fyicenter
SQL> CREATE USER DEV IDENTIFIED BY developer ACCOUNT UNLOCK;
User created.
SQL> disconnect
SQL> CONNECT DEV/developer
ORA-01045: user DEV lacks CREATE SESSION privilege;
logon denied
Oracle error message is pretty clear.
13. How To Grant CREATE SESSION Privilege to a User?
If you want give a user the CREATE SESSION privilege, you can use the GRANT command. The following tutorial exercise shows you how to grant DEV the privilege to connect to the server:
>.\bin\sqlplus /nolog
SQL> connect SYSTEM/fyicenter
SQL> GRANT CREATE SESSION TO dev;
Grant succeeded.
SQL> disconnect
SQL> CONNECT DEV/developer
Connected.
14. How To Revoke CREATE SESSION Privilege from a User?
If you take away the CREATE SESSION privilege from a user, you can use the REVOKE command as shown in the following example script:
>.\bin\sqlplus /nolog
SQL> connect SYSTEM/fyicenter
SQL> REVOKE CREATE SESSION FROM dev;
Revoke succeeded.
SQL> GRANT CREATE SESSION TO dev;
Grant succeeded.
This script restored the CREATE SESSION privilege to user "dev", so you can continue other example scripts below.
15. How To Lock and Unlock a User Account?
If you want to lock a user account for a short period of time, and unlock it later, you can use the ALTER USER ... ACCOUNT command. The following sample script shows how to use this command:
>.\bin\sqlplus /nolog
SQL> connect SYSTEM/fyicenter
SQL> ALTER USER dev ACCOUNT LOCK;
User altered.
SQL> disconnect
SQL> CONNECT DEV/developer
ORA-28000: the account is locked
SQL> disconnect
SQL> connect SYSTEM/fyicenter
SQL> ALTER USER dev ACCOUNT UNLOCK;
User altered.
SQL> disconnect
SQL> CONNECT DEV/developer
Connected.
16. What Privilege Is Needed for a User to Create Tables?
To be able to create tables in a user's own schema, the user needs to have the CREATE TABLE privilege, or the CREATE ANY TABLE privilege, which is more powerful, and allows the user to create tables in other user's schema. The following tutorial exercise gives you a good example on CREATE TABLE privilege:
>.\bin\sqlplus /nolog
SQL> CONNECT DEV/developer
SQL> CREATE TABLE fyi (id NUMBER);
ORA-01031: insufficient privileges
SQL> disconnect
SQL> connect SYSTEM/fyicenter
SQL> GRANT CREATE TABLE TO dev;
Grant succeeded.
SQL> disconnect
SQL> CONNECT DEV/developer
SQL> CREATE TABLE fyi (id NUMBER);
ORA-01950: no privileges on tablespace 'SYSTEM'
The above error message tells that user "dev" is not allowed to use the tablespace "SYSTEM". See the next question for answers.
17. How To Assign a Tablespace to a Users?
When you create a new user, Oracle will assign the SYSTEM tablespace to the user by default. If you want to change this, you can assign a different table space to a user using the ALTER USER command. The following tutorial exercise changes user dev's default tablespace, and assigns 4MB of space to dev:
>.\bin\sqlplus /nolog
SQL> CONNECT DEV/developer
SQL> ALTER USER dev DEFAULT TABLESPACE USERS;
User altered.
SQL> ALTER USER dev QUOTA 4M ON USERS;
User altered.
SQL> disconnect
SQL> CONNECT DEV/developer
SQL> CREATE TABLE fyi (id NUMBER);
Table created.
SQL> DROP TABLE fyi;
Table dropped.
SQL> CREATE TABLE fyi (id NUMBER);
Table created.
As you can see, "dev" can create and drop tables now. You can also let "dev" to create tables in any tablespace without any restriction by granting him the UNLIMITED TABLESPACE system privilege.
18. What Privilege Is Needed for a User to Create Views?
To be able to create views in a user's own schema, the user needs to have the CREATE VIEW privilege, or the CREATE ANY VIEW privilege, which is more powerful, and allows the user to create views in other user's schema. The following tutorial exercise gives you a good example on CREATE VIEW privilege:
>.\bin\sqlplus /nolog
SQL> CONNECT DEV/developer
SQL> CREATE VIEW fyi_view AS SELECT * FROM fyi;
ORA-01031: insufficient privileges
SQL> disconnect
SQL> connect SYSTEM/fyicenter
SQL> GRANT CREATE VIEW TO dev;
Grant succeeded.
SQL> disconnect
SQL> CONNECT DEV/developer
SQL> CREATE VIEW fyi_view AS SELECT * FROM fyi;
View created.
SQL> DROP VIEW fyi_view;
View dropped.
SQL> CREATE VIEW fyi_view AS SELECT * FROM fyi;
View created.
As you can see, "dev" can create and drop views now.
19. What Privilege Is Needed for a User to Create Indexes?
For a user to create indexes, he/she needs the same privilege as the creating tables. Just make sure he/she has the CREATE TABLE privilege. The following tutorial exercise gives you a good example on creating view privilege:
>.\bin\sqlplus /nolog
SQL> connect SYSTEM/fyicenter
SQL> GRANT CREATE TABLE TO dev;
Grant succeeded.
SQL> disconnect
SQL> CONNECT DEV/developer
SQL> CREATE INDEX fyi_index ON fyi(id);
Index created.
SQL> DROP INDEX fyi_index;
Index dropped.
SQL> CREATE INDEX fyi_index ON fyi(id);
Index created.
This exercise assumes that you followed previous exercises in the FAQ collection.
20. What Privilege Is Needed for a User to Query Tables in Another Schema?
For a user to run queries (SELECT statements) on tables of someone else's schema, he/she needs the SELECT ANY TABLE privilege. The following tutorial exercise gives you a good example of granting "dev" to query tables in "hr" schema:
>.\bin\sqlplus /nolog
SQL> CONNECT DEV/developer
SQL> SELECT COUNT(*) FROM hr.employees;
ORA-01031: insufficient privileges
SQL> disconnect
SQL> connect SYSTEM/fyicenter
SQL> GRANT SELECT ANY TABLE TO dev;
Grant succeeded.
SQL> disconnect
SQL> CONNECT DEV/developer
SQL> SELECT COUNT(*) FROM hr.employees;
COUNT(*)
----------
107
As you can see, "dev" can query tables in any schema now.
You also need to remember that table name must be prefixed with the schema name (same as owner user name).
21. What Privilege Is Needed for a User to Insert Rows to Tables in Another Schema?
For a user to insert rows into tables of someone else's schema, he/she needs the INSERT ANY TABLE privilege. The following tutorial exercise gives you a good example of granting "dev" to insert rows in "hr" schema:
>.\bin\sqlplus /nolog
SQL> CONNECT DEV/developer
SQL> INSERT INTO hr.jobs
VALUES ('DV.FYI', 'Dev FYI Consultant', 7700, 8800);
ORA-01031: insufficient privileges
SQL> disconnect
SQL> connect SYSTEM/fyicenter
SQL> GRANT INSERT ANY TABLE TO dev;
Grant succeeded.
SQL> disconnect
SQL> CONNECT DEV/developer
SQL> INSERT INTO hr.jobs
VALUES ('DV.FYI', 'Dev FYI Consultant', 7700, 8800);
1 row created.
As you can see, "dev" can insert rows in any schema now. But you should be careful when giving this privilege to a regular developer.
22. What Privilege Is Needed for a User to Delete Rows from Tables in Another Schema?
For a user to delete rows from tables of someone else's schema, he/she needs the DELETE ANY TABLE privilege. The following tutorial exercise gives you a good example of granting "dev" to delete rows in "hr" schema:
>.\bin\sqlplus /nolog
SQL> CONNECT DEV/developer
SQL> DELETE FROM hr.jobs WHERE job_id = 'DV.FYI';
ORA-01031: insufficient privileges
SQL> disconnect
SQL> connect SYSTEM/fyicenter
SQL> GRANT DELETE ANY TABLE TO dev;
Grant succeeded.
SQL> disconnect
SQL> CONNECT DEV/developer
SQL> DELETE FROM hr.jobs WHERE job_id = 'DV.FYI';
1 row deleted.
As you can see, "dev" can delete rows in any schema now. But you should be careful when giving this privilege to a regular developer.
23. How To Find Out What Privileges a User Currently Has?
Privileges granted to users are listed in two system views: DBA_SYS_PRIVS, and USER_SYS_PRIVS. You can find out what privileges a user currently has by running a query on those views as shown in the tutorial exercise below:
>.\bin\sqlplus /nolog
SQL> CONNECT DEV/developer
SQL> SELECT username, privilege FROM USER_SYS_PRIVS;
USERNAME PRIVILEGE
------------------------- ----------------------
DEV SELECT ANY TABLE
DEV INSERT ANY TABLE
DEV CREATE SESSION
DEV CREATE VIEW
DEV DELETE ANY TABLE
DEV CREATE ANY TABLE
SQL> disconnect
SQL> connect SYSTEM/fyicenter
SQL> GRANT DELETE ANY TABLE TO dev;
Grant succeeded.
SQL> SELECT GRANTEE, PRIVILEGE FROM DBA_SYS_PRIVS
WHERE GRANTEE = 'HR';
GRANTEE PRIVILEGE
------------------------- -----------------------
HR CREATE VIEW
HR UNLIMITED TABLESPACE
HR DEBUG CONNECT SESSION
HR CREATE DATABASE LINK
HR CREATE SEQUENCE
HR CREATE SESSION
HR DEBUG ANY PROCEDURE
HR ALTER SESSION
HR CREATE SYNONYM
Looks like "hr" has move privileges than "dev".
Posts
0 comments:
Post a Comment