Difference between revisions of "Users"

From SQLZOO
Jump to navigation Jump to search
(Blanked the page)
 
Line 1: Line 1:
Create a new user.
 
<div class='ht'>
 
<div class=params>schema:gisq</div>
 
<div>
 
Give the new user permission to connect and to create their own tables etc.
 
</div>
 
<source lang=sql class='tidy'>
 
</source>
 
<source lang=sql class='setup'></source>
 
<source lang='sql' class='def e-sqlite'>CREATE USER scott IDENTIFIED BY 'tiger'
 
</source>
 
<source lang='sql' class='def e-db2'>Use operating system.
 
</source>
 
<source lang='sql' class='def e-ingres'>create user scott with password = 'tiger'
 
grant all on database scott to scott
 
grant access on database gisq to scott
 
</source>
 
<source lang='sql' class='def e-access'>CREATE USER scott IDENTIFIED BY 'tiger'
 
</source>
 
<source lang='sql' class='def e-postgres'>CREATE USER scott WITH PASSWORD 'tiger';
 
CREATE DATABASE scott;
 
GRANT ALL ON DATABASE scott to scott;
 
</source>
 
<source lang='sql' class='def e-oracle'>CREATE USER scott IDENTIFIED BY tiger
 
  TEMPORARY TABLESPACE temp
 
  DEFAULT TABLESPACE users;
 
GRANT CONNECT TO scott;
 
GRANT RESOURCE TO scott
 
</source>
 
<source lang='sql' class='def e-sqlserver'>CREATE LOGIN scott
 
    WITH PASSWORD = 'tiger';
 
CREATE DATABASE scottsdb;
 
USE scottsdb;
 
CREATE USER scott FOR LOGIN scott;
 
</source>
 
<source lang='sql' class='def e-mysql'>--The foolowing doesn't work since 5.0
 
--INSERT INTO mysql.user (user, host, password)
 
--VALUES ('scott', 'localhost', PASSWORD('tiger'));
 
  
CREATE DATABASE scott;
 
 
GRANT SELECT, INSERT,UPDATE,DELETE,CREATE,DROP
 
  ON scott.* TO scott@localhost
 
  IDENTIFIED BY 'tiger';
 
 
FLUSH PRIVILEGES;
 
</source>
 
<source lang='sql' class='def e-mimer'>CREATE IDENT scott AS USER USING 'tiger';
 
CREATE DATABANK userBank;
 
GRANT TABLE ON userBank TO scott;
 
</source>
 
<source lang='sql' class='def e-sybase'>CREATE USER scott IDENTIFIED BY 'tiger'</source>
 
<div class="ecomm e-db2" style="display: none">Create a user from the operating system. Have that user run the command:
 
. /home/db2inst1/sqllib/db2profile
 
as part of the .bashrc (or whatever). </div>
 
<div class="ecomm e-access" style="display: none">Access is a single user system. (Please email me if you know otherwise.)</div>
 
<div class="ecomm e-postgres" style="display: none">If scott is the name of a Unix account there is no problem - otherwise you must insert the following line before the default lines
 
local scott template1 password
 
to the file /var/lib/postgres/data/pg_hba.conf this allows postgres user scott to connect to database template1.</div>
 
<div class="ecomm e-oracle" style="display: none">You should specify the "tablespace" - if you don't it defaults to SYS which causes a whole world of pain.
 
The CONNECT and RESOURCE grants permit users to connect to the system and to create stuff.</div>
 
<div class="ecomm e-sqlserver" style="display: none">[http://msdn.microsoft.com/en-us/library/ms173463.aspx CREATE USER]
 
Old Style:
 
sp_addlogin 'scott', 'tiger';
 
CREATE DATABASE scott;
 
USE scott;
 
sp_changedbowner scott</div>
 
<div class="ecomm e-mimer" style="display: none">An IDENT is a user. A DATABANK corresponds to a file where the data is actually stored. Users normally have a SCHEMA - this is created as the user is created. </div>
 
</div>
 
{{Users ref}}
 

Latest revision as of 13:03, 17 July 2012