в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:05:44Z

1 версия

Новая страница

== Assign CONNECT and RESOURCE Roles==



<source lang="sql">
SQL>
SQL> CREATE USER Alice IDENTIFIED BY simplepassword;
User created.
SQL>
SQL> GRANT CONNECT, RESOURCE to Alice;
Grant succeeded.
SQL>
SQL>
SQL> drop user alice;
User dropped.
SQL>
SQL></source>

== Assign object privileges to roles==



<source lang="sql">
SQL> GRANT SELECT, INSERT ON employee TO entry;

SQL> GRANT SELECT, INSERT ON customer TO entry;
SQL> GRANT SELECT, UPDATE ON employee TO maintenance;
SQL> GRANT SELECT, UPDATE ON customer TO maintenance;
SQL> GRANT EXECUTE ON validate_salary TO maintenance;
SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON employee TO manager;
SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON customer TO manager;
SQL> GRANT EXECUTE ON validate_salary TO manager;
SQL> GRANT EXECUTE ON adjust_salary TO manager;
SQL>
SQL>
SQL></source>

== Assign Role to User==



<source lang="sql">
SQL>
SQL> GRANT TestRole TO Alice;
SQL></source>

== Checking Object Privileges Granted to a Role==

You can check which object privileges have been granted to a role by querying role_tab_privs.



<source lang="sql">
SQL> desc role_tab_privs;
Name Null? Type
ROLE NOT NULL VARCHAR2(30) --User to whom the privilege was granted.
OWNER NOT NULL VARCHAR2(30) --User who owns the object.
TABLE_NAME NOT NULL VARCHAR2(30) --Name of the object on which privilege was granted.
COLUMN_NAME VARCHAR2(30) --Name of the column (if applicable).
PRIVILEGE NOT NULL VARCHAR2(40) --Privilege on the object.
GRANTABLE VARCHAR2(3) --Whether the privilege was granted with the GRANT option. Equal to YES or NO.
SELECT *
FROM role_tab_privs;</source>

== Checking Roles Granted to a User==

You can check which roles have been granted to a user by querying user_role_privs.
A user who creates a role is also granted that role by default.



<source lang="sql">
SQL> desc user_role_privs;
Name Type Description
USERNAME VARCHAR2(30) --Name of the user to whom the role has been granted.
GRANTED_ROLE VARCHAR2(30) --Name of the role granted to the user.
ADMIN_OPTION VARCHAR2(3) --Whether the user is able to grant the role to another user or role. Equal to YES or NO.
DEFAULT_ROLE VARCHAR2(3) --Whether the role is enabled by default when the user connects to the database. Equal to YES or NO.
OS_GRANTED VARCHAR2(3) --Whether the role was granted by the operating system.
SELECT * FROM user_role_privs;</source>

== Checking System Privileges Granted to a Role==

You can check which system privileges have been granted to a role by querying role_sys_privs.



<source lang="sql">
SQL> desc role_sys_privs;
Name Null? Type
ROLE NOT NULL VARCHAR2(30) --Name of the role.
PRIVILEGE NOT NULL VARCHAR2(40) --System privilege granted to the role.
ADMIN_OPTION VARCHAR2(3) --Whether the privilege was granted with the ADMIN option. Equal to YES or NO.

SELECT *
FROM role_sys_privs;</source>

== Creating Roles==



<source lang="sql">
GRANT CREATE ROLE TO store;
GRANT CREATE USER TO store WITH ADMIN OPTION;
CREATE ROLE product_manager;
CREATE ROLE hr_manager;
CREATE ROLE overall_manager IDENTIFIED by manager_password;</source>

== Default Roles==



<source lang="sql">
ALTER USER steve DEFAULT ROLE ALL EXCEPT overall_manager;
SET ROLE NONE;
SET ROLE ALL EXCEPT overall_manager;</source>

== Dropping a Role==



<source lang="sql">
CREATE ROLE product_manager;
GRANT SELECT, INSERT, UPDATE, DELETE ON employee TO product_manager;
REVOKE ALL ON products FROM product_manager;
DROP ROLE product_manager;</source>

== Drop Role==



<source lang="sql">
SQL>
SQL> DROP ROLE TestRole;
Role dropped.</source>

== Granting Privileges to Roles==

You can grant both system and object privileges to a role, as well as grant another role to a role.



<source lang="sql">
GRANT SELECT, INSERT, UPDATE, DELETE ON tableNames TO product_manager;
GRANT CREATE USER TO hr_manager;
GRANT product_manager, hr_manager TO overall_manager;</source>

== Granting Roles to a User==

You grant a role to a user using GRANT.



<source lang="sql">
GRANT overall_manager TO userName;</source>

== Output session roles from procedure==



<source lang="sql">
SQL>
SQL> create or replace procedure myProcedure
2 authid current_user
3 as
4 begin
5 for rec in (select * from session_roles)
6 loop
7 dbms_output.put_line(rec.role);
8 end loop;
9 end;
10 /
Procedure created.
SQL> execute myProcedure
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
SCHEDULER_ADMIN
XDBADMIN
XDBWEBSERVICES
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop procedure myProcedure;
Procedure dropped.
SQL></source>

== Query user granted roles==



<source lang="sql">
SQL>
SQL>
SQL> COL grantee FORMAT A8
SQL> COL granted_role FORMAT A30
SQL> COL grantor FORMAT A8
SQL> COL privilege FORMAT A12
SQL> COL owner FORMAT A4
SQL> COL table_name FORMAT A30
SQL>
SQL>
SQL> SELECT grantee
2 , granted_role
3 FROM dba_role_privs
4 WHERE grantee = "PLSQL";
GRANTEE GRANTED_ROLE
-------- ------------------------------
PLSQL CTXAPP
PLSQL CONNECT
PLSQL RESOURCE
3 rows selected.
SQL>
SQL> -- Query resources.
SQL> SELECT grantor
2 , owner
3 , table_name
4 , grantee
5 , privilege
6 FROM dba_tab_privs
7 WHERE grantee = "PLSQL";
no rows selected</source>

== Regina and Kristen are manager level users==



<source lang="sql">
SQL> CREATE ROLE manager;
Role created.
SQL>
SQL> CREATE USER regina identified by regina;
User created.
SQL> CREATE USER kristen identified by kristen;
User created.
SQL> GRANT CONNECT TO regina, kristen;
Grant succeeded.
SQL> GRANT manager TO regina, kristen;
Grant succeeded.
SQL>
SQL> drop user regina;
User dropped.
SQL> drop user kristen;
User dropped.
SQL>
SQL> drop role manager;
Role dropped.</source>

== Revoking a Role==



<source lang="sql">
REVOKE overall_manager FROM steve;</source>

== Revoking Privileges from a Role==



<source lang="sql">
CREATE ROLE product_manager;
GRANT SELECT, INSERT, UPDATE, DELETE ON employee TO product_manager;
REVOKE ALL ON products FROM product_manager;
DROP ROLE product_manager;</source>

== Rich and Brad are maintenance level users==



<source lang="sql">
SQL> CREATE ROLE maintenance;
Role created.
SQL>
SQL> CREATE USER rich identified by rich;
User created.
SQL> CREATE USER brad identified by brad;
User created.
SQL> GRANT CONNECT TO rich, brad;
Grant succeeded.
SQL> GRANT maintenance TO rich, brad;
Grant succeeded.
SQL>
SQL> drop user rich;
User dropped.
SQL> drop user brad;
User dropped.
SQL> drop role maintenance;
Role dropped.</source>

== Roles==

<OL><LI>A role is a group of privileges that you can assign to a user or another role.</LI><LI>You can grant a role to multiple users and roles.</LI><LI>When adding or deleting a privilege from a role, all users and roles assigned that role automatically receive or lose that privilege.</LI><LI>You can assign multiple roles to a user or role.</LI><LI>You can assign a password to a role.</LI></OL>
36. 7. Roles
36. 7. 1.
Roles
36. 7. 2.
<A href="/Tutorial/Oracle/0700__User-Privilege/CreatingRoles.htm">Creating Roles</a>
36. 7. 3.
<A href="/Tutorial/Oracle/0700__User-Privilege/GrantingPrivilegestoRoles.htm">Granting Privileges to Roles</a>
36. 7. 4.
<A href="/Tutorial/Oracle/0700__User-Privilege/GrantingRolestoaUser.htm">Granting Roles to a User</a>
36. 7. 5.
<A href="/Tutorial/Oracle/0700__User-Privilege/AssignRoletoUser.htm">Assign Role to User</a>
36. 7. 6.
<A href="/Tutorial/Oracle/0700__User-Privilege/CheckingRolesGrantedtoaUser.htm">Checking Roles Granted to a User</a>
36. 7. 7.
<A href="/Tutorial/Oracle/0700__User-Privilege/CheckingSystemPrivilegesGrantedtoaRole.htm">Checking System Privileges Granted to a Role</a>
36. 7. 8.
<A href="/Tutorial/Oracle/0700__User-Privilege/CheckingObjectPrivilegesGrantedtoaRole.htm">Checking Object Privileges Granted to a Role</a>
36. 7. 9.
<A href="/Tutorial/Oracle/0700__User-Privilege/AssignCONNECTandRESOURCERoles.htm">Assign CONNECT and RESOURCE Roles</a>
36. 7. 10.
<A href="/Tutorial/Oracle/0700__User-Privilege/DefaultRoles.htm">Default Roles</a>
36. 7. 11.
<A href="/Tutorial/Oracle/0700__User-Privilege/RevokingaRole.htm">Revoking a Role</a>
36. 7. 12.
<A href="/Tutorial/Oracle/0700__User-Privilege/RevokingPrivilegesfromaRole.htm">Revoking Privileges from a Role</a>
36. 7. 13.
<A href="/Tutorial/Oracle/0700__User-Privilege/DroppingaRole.htm">Dropping a Role</a>
36. 7. 14.
<A href="/Tutorial/Oracle/0700__User-Privilege/ReginaandKristenaremanagerlevelusers.htm">Regina and Kristen are manager level users</a>
36. 7. 15.
<A href="/Tutorial/Oracle/0700__User-Privilege/RichandBradaremaintenancelevelusers.htm">Rich and Brad are maintenance level users</a>
36. 7. 16.
<A href="/Tutorial/Oracle/0700__User-Privilege/UnassignRole.htm">Unassign Role</a>
36. 7. 17.
<A href="/Tutorial/Oracle/0700__User-Privilege/DropRole.htm">Drop Role</a>
36. 7. 18.
<A href="/Tutorial/Oracle/0700__User-Privilege/Assignobjectprivilegestoroles.htm">Assign object privileges to roles</a>
36. 7. 19.
<A href="/Tutorial/Oracle/0700__User-Privilege/UnassignRolefromUser.htm">Unassign Role from User</a>
36. 7. 20.
<A href="/Tutorial/Oracle/0700__User-Privilege/Outputsessionrolesfromprocedure.htm">Output session roles from procedure</a>
36. 7. 21.
<A href="/Tutorial/Oracle/0700__User-Privilege/Queryusergrantedroles.htm">Query user granted roles</a>

== Unassign Role==



<source lang="sql">
SQL>
SQL> CREATE USER Alice IDENTIFIED BY simplepassword;
User created.
SQL>
SQL> GRANT CONNECT, RESOURCE to Alice;
Grant succeeded.
SQL>
SQL> REVOKE RESOURCE FROM Alice;
Revoke succeeded.
SQL>
SQL> drop user alice;
User dropped.
SQL>
SQL>
SQL></source>

== Unassign Role from User==



<source lang="sql">
SQL>
SQL> REVOKE TestRole FROM Alice;</source>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL Tutorial/User Privilege/Roles - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия