Oracle System Privileges (DBA)

السلام عليكم

  DBA سنتحدث اليوم عن صلاحيات مدير النظام

يوجد اكتر من 100 صلاحيه لمدير النظام بأمكانه استخدامها او منحها لـ مستخدمين اخرين 

على سبيل المثال

CREATE USER

بأمكانه انشاء مستخدم جديد او اعطاء صلاحيه انشاء مستخدم جديد فى اى اسكيما لـ اى مستخدم اخر فى اى اسكيما

DROP USER

بأمكانه مسح اى مستخدم او اعطاء الصلاحيه لذلك لـ اى مستخدم اخر

DROP ANY TABLE

 او اعطاء الصلاحيه لذلك -SCHEMA- بامكانه مسح اى جدول داخل اى اسكيما

BACKUP ANY TABLES

EXPORT UTILITY بأمكانه اخد نسخه احتياطيه من اى اسكيما واعطاء الصلاحيات لذلك بأستخدام اى  

SELECT ANY TABLE

 فى اى اسكيما واعطاء الصلاحيات لذلك VIEW OR SNAPSHOTS بأمكانه الاستعلام عن اى جدول او 

CREATE ANY TABLE

بامكانه بناء اى جدول فى اى اسكيما واعطاء الصلاحيات لذلك

 DBA فى الجدول التالى يوضح اغلب صلاحيات ال 

Data Dictionary Objects Related To System Privileges	all_sys_privs session_privs user_sys_privs dba_sys_privs system_privilege_map
Administer	Administer Any SQL Tuning Set Administer Database Trigger (database level trigger) Administer Resource Manager Administer SQL Management Object Administer SQL Tuning Set Flashback Archive Administrator Grant Any Object Privilege Grant Any Privilege Grant Any Role Manage Scheduler Manage Tablespace
Advanced Queuing	Dequeue Any Queue Enqueue Any Queue Manage Any Queue
Advisor Framework	Advisor Administer SQL Tuning Set Administer Any SQL Tuning Set Administer SQL Management Object Alter Any SQL Profile Create Any SQL Profile Drop Any SQL Profile
Alter Any Privileges	Alter Any Cluster Alter Any Cube Alter Any Cube Dimension Alter Any Dimension Alter Any Evaluation Context Alter Any Index Alter Any Indextype Alter Any Library Alter Any Materialized View Alter Any Mining Model Alter Any Operator Alter Any Outline Alter Any Procedure Alter Any Role Alter Any Rule Alter Any Rule Set Alter Any Sequence Alter Any SQL Profile Alter Any Table Alter Any Trigger Alter Any Type
Alter Privileges	Alter Database Alter Profile Alter Resource Cost Alter Rollback Segment Alter Session Alter System Alter Tablespace Alter User
Analyze Privileges	Analyze Any Analyze Any Dictionary
Audit Privileges	Audit Any Audit System
Backup Privileges	Backup Any Table
Change Privilege	Change Notification
Clusters	Alter Any Cluster Create Cluster Create Any Cluster Drop Any Cluster
Comment Privileges	Comment Any Mining Model Comment Any Table
Contexts	Create Any Context Drop Any Context
Create Any Privileges	Create Any Cluster Create Any Context Create Any Cube Create Any Cube Build Process Create Any Cube Dimension Create Any Dimension Create Any Directory Create Any Evaluation Context Create Any Index Create Any Indextype Create Any Job Create Any Library Create Any Materialized View Create Any Measure Folder Create Any Mining Model Create Any Operator Create Any Outline Create Any Procedure Create Any Rule Create Any Rule Set Create Any Sequence Create Any SQL Profile Create Any Synonym Create Any Table Create Any Trigger Create Any Type Create Any View
Create Privileges	Create Cluster Create Cube Create Cube Build Process Create Cube Dimension Create Database Link Create Dimension Create Evaluation Context Create External Job Create Indextype Create Job Create Library Create Materialized View Create Measure Folder Create Mining Model Create Operator Create Procedure Create Profile Create Public Database Link Create Public Synonym Create Role Create Rollback Segment Create Rule Create Rule Set Create Sequence Create Session Create Synonym Create Table Create Tablespace Create Trigger Create Type Create User Create View
Database	Alter Database Alter System Audit System
Database Links	Create Database Link Create Public Database Link Drop Public Database Link
Debug	Debug Any Procedure Debug Connect Session
Delete	Delete Any Cube Dimension Delete Any Measure Folder Delete Any Table
Dimensions	Alter Any Dimension Create Any Dimension Create Dimension Drop Any Dimension
Directories	Create Any Directory Drop Any Directory
Drop Any Privileges	Drop Any Cluster Drop Any Context Drop Any Cube Drop Any Cube Build Process Drop Any Cube Dimension Drop Any Dimension Drop Any Directory Drop Any Evaluation Context Drop Any Index Drop Any Indextype Drop Any Library Drop Any Materialized View Drop Any Measure Folder Drop Any Mining Model Drop Any Operator Drop Any Outline Drop Any Procedure Drop Any Role Drop Any Rule Drop Any Rule Set Drop Any Sequence Drop Any SQL Profile Drop Any Synonym Drop Any Table Drop Any Trigger Drop Any Type Drop Any View
Drop Privileges	Drop Profile Drop Public Database Link Drop Public Synonym Drop Rollback Segment Drop Tablespace Drop User
Evaluation Context	Alter Any Evaluation Context Create Any Evaluation Context Create Evaluation Context Drop Any Evaluation Context Execute Any Evaluation Context
Execute Any Privileges	Execute Any Class Execute Any Evaluation Context Execute Any Indextype Execute Any Library Execute Any Operator Execute Any Procedure Execute Any Program Execute Any Rule Execute Any Rule Set Execute Any Type
Export & Import	Export Full Database Import Full Database
Fine Grained Access Control	Exempt Access Policy
File Group	Manage Any File Group Manage File Group Read Any File Group
Flashback	Flashback Any Table Flashback Archive Administrator
Force	Force Any Transaction Force Transaction
Indexes	Alter Any Index Create Any Index Drop Any Index
Indextype	Alter Any Indextype Create Any Indextype Create Indextype Drop Any Indextype Execute Any Indextype
Insert	Insert Any Cube Dimension Insert Any Measure Folder Insert Any Table
Job Scheduler	Create Any Job Create External Job Create Job Execute Any Class Execute Any Program Manage Scheduler
Libraries	Alter Any Library Create Any Library Create Library Drop Any Library Execute Any Library
Locks	Lock Any Table
Materialized Views	Alter Any Materialized View Create Any Materialized View Create Materialized View Drop Any Materialized View Flashback Any Table Global Query Rewrite On Commit Refresh Query Rewrite
Mining Models	Alter Any Mining Model Comment Any Mining Model Create Any Mining Model Create Mining Model Drop Any Mining Model Select Any Mining Model
OLAP Cubes	Alter Any Cube Create Any Cube Create Cube Drop Any Cube Select Any Cube Update Any Cube
OLAP Cube Build	Create Any Cube Build Process Create Cube Build Process Drop Any Cube Build Process Update Any Cube Build Process
OLAP Cube Dimensions	Alter Any Cube Dimension Create Any Cube Dimension Create Cube Dimension Delete Any Cube Dimension Drop Any Cube Dimension Insert Any Cube Dimension Select Any Cube Dimension Update Any Cube Dimension
OLAP Cube Measure Folders	Create Any Measure Folder Create Measure Folder Delete Any Measure Folder Drop Any Measure Folder Insert Any Measure Folder
Operator	Alter Any Operator Create Any Operator Create Operator Drop Any Operator Execute Any Operator
Outlines	Alter Any Outline Create Any Outline Drop Any Outline
Procedures	Alter Any Procedure Create Any Procedure Create Procedure Drop Any Procedure Execute Any Procedure
Profiles	Alter Profile Create Profile Drop Profile
Query Rewrite	Global Query Rewrite Query Rewrite
Refresh	On Commit Refresh
Resumable	Resumable
Roles	Alter Any Role Create Role Drop Any Role Grant Any Role
Rollback Segment	Alter Rollback Segment Create Rollback Segment Drop Rollback Segment
Scheduler	Manage Scheduler
Select	Select Any Cube Select Any Cube Dimension Select Any Dictionary Select Any Mining Model Select Any Sequence Select Any Table Select Any Transaction
Sequence	Alter Any Sequence Create Any Sequence Create Sequence Drop Any Sequence Select Any Sequence
Session	Alter Resource Cost Alter Session Create Session Restricted Session
Synonym	Create Any Synonym Create Public Synonym Create Synonym Drop Any Synonym Drop Public Synonym
Sys Privileges	SYSDBA SYSOPER
Tablespace	Alter Tablespace Create Tablespace Drop Tablespace Manage Tablespace Unlimited Tablespace
Table	Alter Any Table Backup Any Table Comment Any Table Create Any Table Create Table Delete Any Table Drop Any Table Flashback Any Table Insert Any Table Lock Any Table Select Any Table Update Any Table
Transaction	Force Any Transaction Force Transaction
Trigger	Administer Database Trigger Alter Any Trigger Create Any Trigger Create Trigger Drop Any Trigger
Types	Alter Any Type Create Any Type Create Type Drop Any Type Execute Any Type Under Any Type
Update	Update Any Cube Update Any Cube Build Process Update Any Cube Dimension Update Any Table
Under	Under Any Table Under Any Type Under Any View
User	Alter User Become User Create User Drop User
View	Create Any View Create View Drop Any View Flashback Any Table Merge Any View Under Any View

http://psoug.org مصدر الجدول 

الكود التالى يوضح لك المستخدمين وصلاحياتهم

Code

SELECT path
FROM (
  SELECT grantee,
    sys_connect_by_path(privilege, ':')||':'||grantee path
  FROM (
    SELECT grantee, privilege, 0 role
    FROM dba_sys_privs
    UNION ALL
    SELECT grantee, granted_role, 1 role
    FROM dba_role_privs)
  CONNECT BY privilege=prior grantee
  START WITH role = 0)
WHERE grantee IN (
  SELECT username
  FROM dba_users
  WHERE lock_date IS NULL
  AND password != 'EXTERNAL'
  AND username != 'SYS')
OR grantee='PUBLIC'
/

السلام عليكم

User System Privileges 

Schema سنتحدث اليوم عن الصلاحيات التى يمكن اعطائها للمستخدمين على مستوى الـ

  بأعطاء بعض الصلاحيات للمستخدم على سبيل المثال DBA بعد انشاء مستخدم جديد يقوم الـ

CREATE SESSION 

تمكنك من الدخول على قاعده البيانات

CREATE TABLE

تمكنك من انشاء جدول داخل ال سكيما الخاصه بك

CREATE SEQUENCE

داخل الاسكيما squance تمكنك من انشاء 

CREATE VIEW

داخل الاسكيما view تمكنك من انشاء 

CREATE PROCEDURE 

داخل الاسكيما stored procedure , function or packages تمكنك من انشاء 

الكود التالى يوضح بشكل عام كيفيه اعطاء الصلاحيات للمستخدمين

Code

GRANT privilege [, privilege...]
TO user [, user| role, PUBLIC...];

SCOTT مثال لـ أعطاء بعض الصلاحيات للمستخدم 

Code

GRANT CREATE SESSION
             , CREATE SEQUENCE
             , CREATE VIEW
             , CREATE TABLE
TO        SCOTT;

يستخدم الـ DBA امر GRANT لـ اعطاء الصلاحيات للمستخدمين وبعد ان يتم منح اى مستخدم الصلاحيات المطلوبه بأمكانه استخدامها فى الحال