Tuesday, August 15, 2006

DCL : Privileges (Grant/Revoke)

GRANT privilege [, privilege...] TO user [, user | role, PUBLIC] [WITH ADMIN OPTION];
GRANT obj_privilege [(columns)] ON object TO user [, user | role, PUBLIC] [WITH GRANT OPTION];
REVOKE {privilege [, privilege...] | ALL } ON object FROM user [,user | role, PUBLIC] [CASCADE CONSTRAINTS];

System Privileges

Note: Initialization parameter 07_DIRECTORY_ACCESSIBILITY is used to prevent regular users with 'UPDATE ANY TABLE' privilege from accessing the data dictionary.


FALSE:

This is the default value. Access to objects in the SYS schema, including data dictionary, restricted to users who connects as SYSDBA & user SYS. In this case, system privileges which allow access to any schemas, don't allow access to objects in the dictionary schema.

TRUE :

Users with access to ANY SCHEMA are also given access to SYS schema objects. (This is the Oracle 7 behaviour.)



There are more than 100 system privileges available.

Typical DBA Privileges:

Typical User / Application Developer Privileges:

Object Privileges:

Object Privilege is a right to perform a certain action on a specific table/view/sequence/procedure.

Granting System Privileges:

GRANT system_privilege|role


[, {system_privilege|role}]...
TO
user|role|public [, {user | role, PUBLIC}]...
[WITH ADMIN OPTION];

Granting Object Privileges:

GRANT obj_privilege [(column_list)]


[, obj_privilege [(column_list)]]...
| ALL [PRIVILEGES]
ON [schema.]object
TO
{user|role|PUBLIC} [, {user | role|PUBLIC}]
[
WITH GRANT OPTION]

Note :

Revoking System Privileges:

REVOKE system_privilege|role


[, {system_privilege|role}]...
FROM
user|role|public [, {user | role, PUBLIC}]...
;

Revoking Object Privileges:

REVOKE {obj_privilege


[, obj_privilege ]...
| ALL [PRIVILEGES]}
ON [schema.]object
FROM
{user|role|PUBLIC} [, {user | role|PUBLIC}]...
[
CASCADE CONSTRAINTS]

Note :

System Privileges:

Admin functions

alter system, audit system, audit any, alter database, analyze any, SYSDBA, SYSOPER, grant any privilege.

Database access

Create session, alter session, restricted session

Tablespaces

Create | alter | manage | drop | unlimited tablespace.

Note : You can't grant unlimited tablespace to a role.

Users

Create user, become user, alter user, drop user

Undo Segments

Create rollback segment, alter rollback segment, drop rollback segment.

Note : These privileges are not needed if automatic undo management is used.

Tables

Create table, create | alter | backup | insert | update | delete any table

Note :

  • Create privilege also enables drop privilege.

  • Create table also enables creation of indexes & run analyze command on the table.

  • To truncate a table you must have drop any table privilege.

Clusters

Create cluster, create any cluster, alter any cluster, drop any cluster

Note :create privilege also enables u to alter or drop those clusters.

Indexes

Create any index, alter any index, drop any index.

Synonyms

Create synonym, create | drop any synonym, create | drop public synonym

Views

Create view, create any view, drop any view

Note : create privilege also enables u to alter or drop views that you own.

Sequences

Create sequence, create | alter | drop | select any sequence

Note :Create privilege also enables you to drop the sequences you own.

Database links

Create database link, create | drop public database link

Note :Create privilege also enables you to drop private database links that you own.

(These are objects in Oracle which allows you to reference tables in another database without need to make a separate connection.)

Roles

Create role, create| drop | grant | alter any role

Transactions

Force transaction, force any transaction

(These privileges are used to resolve in-doubt, distributed transactions.)

PL/SQL

Create procedure, create | alter | drop | execute any procedure

Note : create privilege also enables u to alter and drop PL/SQL blocks that you own.

Triggers

Create trigger, create | alter | drop any trigger

Note : create privilege also enables u to alter and drop triggers that you own.

Profiles

Create | alter | drop profile, alter resource cost

(These are objects in Oracle that allows you to impose limits on resources for users.)

Snapshots and Materialized views

Create snapshot, create | alter | drop any snapshot

(Snapshots are objects in Oracle that enables you to replicate data from a table in one database to a copy of the table in another.)

Directories

Create | drop any directory

(Directories refer to directories on the host machine and are used to identify a directory that contains Objects Oracle keeps track of that are external to Oracle, such as objects of BFILE type.)

Types

Create type, create | alter | drop | execute any type

Note : create privilege also enables u to alter and drop types that you own.

(Types are the user defined types you can create in the Oracle Objects Option.)

Libraries

Create library, create | alter | drop | execute any library

(A library is an object that enables you to reference a set of procedures external to Oracle. Currently on C procedures are supported.)


SYSOPER Privileges

SYSDBA Privileges

  • STARTUP, SHUTDOWN

  • ALTER DATABASE OPEN | MOUNT

  • ALTER DATABASE BACKUP CONTROLFILE

  • RECOVER DATABASE

  • ALTER DATABASE ARCHIVELOG

  • RESTRICTED SESSION?
  • All SYSOPER privileges

  • CREATE DATABASE

  • ALTER DATABASE [BEGIN|END] BACKUP

  • RESTRICTED SESSION

  • RECOVER DATABASE

Object Privileges:


Select

Object privilege to access data in a table, sequence, view or snapshot.

insert

Object privilege to insert data into a table or in a view in some cases.

update

Object privilege to update data into a table or view.

delete

Object privilege to delete data into a table or view.

alter

Object privilege to alter the definition of a table & sequence only (view, procedure?).

Note : The alter privileges on all other database objects is considered System privileges.

index

Object privilege to create an index on a table (or View) already defined.

references

Object privilege to reference data in the referenced table while defining (create or alter) a table with a foreign key constraint.

execute

Object privilege to run a stored procedure or function.

Note : There are only the above eight object privileges, all else are System privileges.

Views:


SESSION_PRIVS

ROLE_SYS_PRIVS

ROLE_TAB_PRIVS

USER_ROLE_PRIVS

:

:

:

:

List of privileges that are currently available to the user

System privileges granted to roles

Table privileges granted to roles

Role accessible to user

USER_TAB_PRIVS_MADE

USER_TAB_PRIVS_RECD

USER_COL_PRIVS_MADE

USER_COL_PRIVS_RECD

USER_SYS_PRIVS

:

:

:

:

:

Object privileges granted on the user's Objects

Object privileges granted to the user

Object privileges granted on the columns of user's Objects

Object privileges granted to the user on specific columns

System privileges granted to the user

DBA_TAB_PRIVS

DBA_COL_PRIVS

DBA_SYS_PRIVS

:

:

:

Lists all granted on all objects in the database

Describes all object-column grants in the database

System privileges granted to the users & roles

Database Links:

CREATE PUBLIC DATABASE LINK hq.sk.com USING 'service_name';

SELECT * FROM emp@hq.sk.com

Usage:

GRANT create session, create table, create sequence, create view TO hr;
CREATE USER user1 IDENTIFIED BY user1;
CREATE USER user2 IDENTIFIED BY user2;

System Privileges

CREATE ROLE manager;
GRANT create table, create procedure TO manager;
GRANT manager, create session to user1, user2;

Data dictionary view of System Privileges:

SELECT * FROM SESSION_PRIVS;

Object Privileges:

GRANT select ON departments TO scott;
GRANT update(department_name, location_id) ON departments TO scott;
REVOKE ALL ON departments FROM scott;

GRANT ALL ON departments TO scott;
REVOKE ALL on departments FROM scott;

Object Privileges to Public:

GRANT select ON departments TO PUBLIC;
REVOKE ALL ON departments FROM PUBLIC;

Revoking Privileges:

Scott gives the following privileges:

GRANT select on table1 to user1 WITH GRANT OPTION

User1 gives the following delegates it to others:

GRANT select on scott.table1 to user2 WITH GRANT OPTION

Scott revokes the following privileges:

REVOKE select on table1 FROM user1;

Result : Both user1 & user2 looses their privileges on scott.table1.


Comments: Post a Comment

Links to this post:

Create a Link



<< Home
Advertisements

eBay.in - The World's Online Marketplace

ARCHIVES
  • March 2006
  • April 2006
  • May 2006
  • June 2006
  • July 2006
  • August 2006
  • September 2006

This page is powered by Blogger. Isn't yours?

Recent Postings

Google