Oracle User Management :#03

Privilege

User Privilege

Authentication means to authenticate a system user account ID for access to an Oracle database.
Authorization means to verify that a system user account ID has been granted the right, called a privilege, to execute a particular type of SQL statement or to access objects belonging to another system user account.
In order to manage system user access and use of various system objects, such as tables, indexes, and clusters, Oracle provides the capability to grant and revoke privileges to individual user accounts.

Example privileges include the right to:

• Connect to a database
• Create a table
• Select rows from another user’s table
• Execute another user’s stored procedure

There are six categories of privileges:

• System privileges allow a system user to perform a specific type of operation or set of operations. Typical operations are creating objects, dropping objects, and altering objects.
• Schema Object privileges allow a system user to perform a specific type of operation on a specific schema object. Typical objects include tables, views, procedures, functions, sequences, etc.
• Table privileges are schema object privileges specifically applicable to Data Manipulation Language (DML) operations and Data Definition Language (DDL) operations for tables.
• View privileges apply to the use of view objects that reference base tables and other views.
• Procedure privileges apply to procedures, functions, and packages
• Type privileges apply to the creation of named types such as object types, VARRAYs, and nested tables.

System Privileges:


The number of system privileges has grown by version to version .
A complete listing is available by querying the view named SYSTEM_PRIVILEGE_MAP.

Privileges can be divided into three categories:
Those enabling system wide operations, for example, CREATE SESSION, CREATE TABLESPACE.
Those enabling the management of an object that is owned by the system user,for example, CREATE TABLE.
Those enabling the management of an object that is owned by any system user, for example, CREATE ANY TABLE.

If you can create an object, such as that privilege provided by the CREATE TABLE privilege, then you can also drop the objects you create.

Category : session
Privilege: Create Session,Alter Session

Category :TABLESPACE
Privilege: Create Tablespace,Alter Tablespace,Drop Tablespace,Unlimited Tablespace

Category :TABLE
Privilege: Create Table,Create Any Table,Alter Any Table,Drop Any Table,Select Any Table

Category :INDEX
Privilege: Create Any Index,Alter Any Index

Some privileges, such as UNLIMITED TABLESPACE cannot be granted to a role.


Granting System Privileges:

The command to grant a system privilege is the GRANT command. Some example GRANT commands are shown here.

sql>GRANT ALTER TABLESPACE, DROP TABLESPACE TO user1;
Grant succeeded.
sql>GRANT CREATE SESSION TO user1 WITH ADMIN OPTION;
Grant succeeded.

In general, you can grant a privilege to either a user or to a role. You can also grant a privilege to PUBLIC - this makes the privilege available to every system user.
The WITH ADMIN OPTION clause enables the grantee (person receiving the privilege) to grant the privilege or role to other system users or roles; however, you cannot use this clause unless you have, yourself, been granted the privilege with this clause.
The GRANT ANY PRIVILEGE system privilege also enables a system user to grant or revoke privileges.
The GRANT ANY ROLE system privilege is a dangerous one that you don't give to the average system user since then the user could grant any role to any other system user.


SYSDBA and SYSOPER Privileges

SYSDBA and SYSOPER are special privileges that should only be granted to a DBA.
This table lists example privileges associated with each of these special privileges.

SYSOPER: STARTUP,SHUTDOWN,ALTER DATABASE OPEN | MOUNT,RECOVER DATABASE,ALTER DATABASE ARCHIVELOG,RESTRICTED SESSION,ALTER DATABASE BEGIN /END BACKUP
SYSDBA : SYSOPER PRIVILEGES THAT INCLUDE THE WITH ADMIN OPTION.CREATE DATABASE,RECOVER DATABASE UNTIL

Displaying System Privileges:

You can display system privileges by querying the DBA_SYS_PRIVS view.

SQL> SELECT * FROM dba_sys_privs WHERE Grantee='USER1' ;
GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
USER1                          ALTER TABLESPACE                         NO
USER1                          DROP TABLESPACE                          NO
USER1                          CREATE SESSION                           YES

SQL>

You can view the users who have SYSOPER and SYSDBA privileges by querying v$pwfile_users

SQL> SELECT * FROM v$pwfile_users;
USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE
SQL>

The view SESSION_PRIVS gives the privileges held by a user for the current logon session.

SQL> conn user1/user1
Connected.
SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
ALTER TABLESPACE
DROP TABLESPACE


Revoking System Privileges :


The REVOKE command can be used to revoke privileges from a system user or from a role. Only privileges granted directly with a GRANT command can be revoked.
There are no cascading effects when a system privilege is revoked. For example, the DBA grants the SELECT ANY TABLE WITH ADMIN OPTION to system user1, and then system user1 grants the SELECT ANY TABLE to system user2, then if system user1 has the privilege revoked, system user2 still has the privilege.

revoke create table from user1;
revoke create session from user2;