в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:05:44Z

1 версия

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

== Create user and grant permission==



<source lang="sql">
SQL>
SQL> create user oracle_admin
2 identified by oracle_admin;
User created.
SQL>
SQL> grant create session, dba
2 to oracle_admin;
Grant succeeded.
SQL>
SQL> drop user oracle_admin;
User dropped.</source>

== CREATE USER userName IDENTIFIED BY password and grant permission==



<source lang="sql">
SQL> CREATE USER user1 IDENTIFIED BY pass1;
User created.
SQL> CREATE USER user2 IDENTIFIED BY pass2;
User created.
SQL> GRANT CREATE SESSION TO user1, user2;
Grant succeeded.
SQL>
SQL> DROP USER user1;
User dropped.
SQL> DROP USER user2;
User dropped.</source>

== Get user name from all_users==



<source lang="sql">
SQL>
SQL> select substr( username, 1, 1 )
2 from all_users au1
3 where rownum = 1;
S
-
S
1 row selected.</source>

== grant administer database trigger to userName==



<source lang="sql">
SQL>
SQL> grant administer database trigger to userName;
SQL>
SQL></source>

== Grant all possible permissions==



<source lang="sql">
SQL>
SQL>
SQL> create user verifyit identified by verify ;
User created.
SQL>
SQL>
SQL> grant create session, create table, create procedure,
2 create sequence, create view, create trigger,
3 create synonym, create materialized view,
4 query rewrite, create any directory, create type,
5 dba, aq_administrator_role, javasyspriv to verifyit ;
dba, aq_administrator_role, javasyspriv to verifyit
*
ERROR at line 5:
ORA-01919: role "JAVASYSPRIV" does not exist

SQL>
SQL> drop user verifyit;
User dropped.
SQL></source>

== Grant connect and resource permission to a user==



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

== Grant connect permission to a user==



<source lang="sql">
SQL>
SQL>
SQL> CREATE USER tempb IDENTIFIED BY tempb;
User created.
SQL>
SQL> GRANT CONNECT TO tempb;
Grant succeeded.
SQL>
SQL> drop user tempb;
User dropped.</source>

== Grant connect to permission to all name in a table==



<source lang="sql">
SQL>
SQL>
SQL> begin
2 for x in (select ename from scott.emp where ename <> "SCOTT")
3 loop
4 execute immediate "grant connect to " || x.ename ||
5 " identified by " || x.ename;
6 end loop;
7 end;
8 /

SQL>
SQL></source>

== grant create any context to userName==



<source lang="sql">
SQL>
SQL> grant create any context to userName;</source>

== Grant CREATE SYNONYM, CREATE VIEW, CREATE DATABASE LINK, CREATE PUBLIC SYNONYM, DROP PUBLIC SYNONYM==



<source lang="sql">
SQL>
SQL>
SQL> GRANT CREATE SYNONYM, CREATE VIEW, CREATE DATABASE LINK,
2 CREATE PUBLIC SYNONYM, DROP PUBLIC SYNONYM TO RESOURCE;
Grant succeeded.
SQL>
SQL></source>

== grant execute on dbms_rls to userName==



<source lang="sql">
SQL>
SQL>
SQL> grant execute on dbms_rls to userName;
SQL>
SQL></source>

== grant select on tableName to userName==



<source lang="sql">
SQL>
SQL>
SQL> grant select on tableName to userName;

SQL></source>

== Grant with admin option==



<source lang="sql">
SQL>
SQL>
SQL> create user demo_ddl identified by demo_ddl;
User created.
SQL>
SQL> grant connect, resource to demo_ddl with admin option;
Grant succeeded.
SQL>
SQL> grant create user to demo_ddl;
Grant succeeded.
SQL> grant drop user to demo_ddl;
Grant succeeded.
SQL>
SQL> --@connect demo_ddl/demo_ddl
SQL>
SQL> drop user demo_ddl cascade;
User dropped.
SQL>
SQL></source>

== REVOKE CREATE TABLE, CREATE CLUSTER, CREATE SYNONYM, CREATE VIEW, CREATE SEQUENCE, CREATE DATABASE LINK FROM CONNECT==



<source lang="sql">
SQL>
SQL>
SQL> REVOKE CREATE TABLE, CREATE CLUSTER, CREATE SYNONYM, CREATE VIEW,
2 CREATE SEQUENCE, CREATE DATABASE LINK FROM CONNECT;

SQL></source>

== Revoke Permissions==



<source lang="sql">
SQL>
SQL>
SQL> CREATE USER Alice IDENTIFIED BY simplepassword;
User created.
SQL>
SQL> GRANT SELECT, INSERT ON emp TO Alice WITH GRANT OPTION;
SQL>
SQL> REVOKE INSERT ON emp FROM Alice;
SQL>
SQL>
SQL> drop user alice;
User dropped.
SQL></source>

== Revoke Permissions with Cascade==



<source lang="sql">
SQL>
SQL>
SQL> CREATE USER Alice IDENTIFIED BY simplepassword;
User created.
SQL>
SQL> GRANT SELECT, INSERT ON emp TO Alice WITH GRANT OPTION;
SQL>
SQL>
SQL> REVOKE INSERT ON emp FROM Alice CASCADE;
SQL>
SQL> drop user alice;
User dropped.
SQL></source>

== To connect to the database a user must be granted the permission to create a session, which is the CREATE SESSION system privilege.==

<p>Permissions are granted by a privileged user (system, for example) using the GRANT statement.</p>
<p>The following example grants the CREATE SESSION permission to jason:</p>



<source lang="sql">
CONNECT system/manager
SQL> CREATE USER jason IDENTIFIED BY pass;
User created.
SQL>
SQL> GRANT CREATE SESSION TO jason;
Grant succeeded.
SQL>
SQL> DROP USER jason;
User dropped.</source>

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

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

в 13:45, 26 мая 2010

Admin: 1 версия