в 13:45, 26 мая 2010

2010-05-26T13:45:19Z

Admin: 1 версия

2010-05-26T09:55:05Z

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 Role to User==



<source lang="sql">

SQL>
SQL> GRANT TestRole TO Alice;
SQL>

</source>

==Creating a Role==



<source lang="sql">

CREATE ROLE new_dba;
drop role new_dba
--

</source>

==define a role as a global role, which means that a user can only be authorized to use the role by an enterprise directory service.==



<source lang="sql">

CREATE ROLE supervisor IDENTIFIED GLOBALLY;
drop role supervisor;
--

</source>

==Drop Role==



<source lang="sql">

SQL>
SQL> DROP ROLE TestRole;
Role dropped.

</source>

==Get two database users and show their roles.==



<source lang="sql">

SQL> REM
SQL> accept user_1 prompt "Enter the first user: "
Enter the first user: accept user_2 prompt "Enter the second: "
SQL> select grantee, granted_role from dba_role_privs where
2 grantee in (upper("&user_1"),upper("&user_2"))
3 order by granted_role, grantee
4 /

</source>

==Grant Permissions to Role==



<source lang="sql">

SQL>
SQL> CREATE ROLE TestRole;
SQL>
SQL> GRANT UPDATE ON emp TO TestRole;
SQL>
SQL>
SQL> drop role Textrole;

</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>

==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>

==use a password when a role is authorized by the database==



<source lang="sql">

CREATE ROLE clerk IDENTIFIED BY password;
drop row clerk;

</source>

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

Oracle PL/SQL/User Previliege/Role - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия